Postgres
Postgres is a high-performance, open source database engine, and also the coolest database engine in existence. If you are using MySQL instead of Postgres you are literally a monster.
psql cheat sheet
Use the psql
command line tool to explore and manage your databases.
# connect to your personal database
psql
# connect to a specific database as current $USER
psql mydatabase
# connect to a specific database as a specific user with a password
psql mydatabase -U user -W
# use a specific host and port
psql mydatabase -h localhost -p 5432
Once in psql
, you can use special commands starting with a backslash as well as standard SQL queries.
Command
What it does
\l
list databases
\c db user
connect to the db
database as user
(user is optional)
\d
list relations (tables, sequences, views, indices)
\d table
describe table
(show columns, types, indices)
\dt
list tables in the current database
\e
use your $EDITOR to write a long query
\timing
toggle display of query execution times
Securing Postgres
This is by no means a comprehensive guide, and it's probably going to grow over time.
pg_hba.conf
Using the /etc/postgresql/[version]/[database]/pg_hba.conf
config file you can easily control which client can log in and to which database, as explained in the documentation. Most common authentication methods:
trust
- use in local development only, unconditionally allows all roles to connectreject
- deny all access from the given IPs or for given userscram-sha-256
- send a hashed password (use this instead of the oldmd5
method for password-based authenticationpeer
- for local connections, use current user's username
Copy a database from one machine to another
Create a database dump on the source machine using the
-O
switch to disable outputting ownership data:psql -U postgres -O <databasename> > dump.sql
Create a new role and a new database on the target system:
psql -c "CREATE ROLE <username> WITH LOGIN; psql -c "CREATE DATABASE <databasename> WITH OWNER <username> ENCODING = 'UNICODE';
Import the data:
psql -U <username> -d <databasename> -f dump.sql
Copy to/from CSV
If you ever need to share some data, you can easily export a request to CSV straight into a file:
\copy (select * from ...) to '/tmp/export.csv' with csv
Or maybe directly to STDOUT?
\copy (select * from ...) to stdout with csv
Or maybe you want to copy from a CSV file into a table?
\copy table from '~/input.csv' with delimiter ',' csv header
Last updated
Was this helpful?