Miscellaneous

Export table into CSV file

\copy table TO '<path>' CSV \copy table(col1,col1) TO '<path>' CSV \copy (SELECT...) TO '<path>' CSV 

Import CSV file into table

\copy table FROM '<path>' CSV \copy table(col1,col1) FROM '<path>' CSV 

See also: Copy

Get location of postgresql.conf

$ psql -U postgres -c 'SHOW config\_file' 

Append to postgresql.conf

listen_addresses = '*' 

Append to pg_hba.conf (Same location as postgresql.conf)

host all all 0.0.0.0/0 md5 host all all ::/0 md5 

Restart PostgreSQL server

$ sudo systemctl restart postgresql 

Restore a database with psql

$ psql -U user mydb < mydb\_backup.sql 

Restore a database with pg_restore

$ pg\_restore -d mydb mydb\_backup.sql -c 

•   -U   Specify a database user
•   -c   Drop database before recreating
•   -C   Create database before restoring
•   -e   Exit if an error has encountered
•   -F   Format (c: custom, d: directory, t: tar, p: plain text sql(default)) Use pg_restore -? to get the full list of options

Use pg_dumpall to backup all databases

$ pg\_dumpall -U postgres > all.sql 

Use pg_dump to backup a database

$ pg\_dump -d mydb -f mydb\_backup.sql 

•   -a   Dump only the data, not the schema
•   -s   Dump only the schema, no data
•   -c   Drop database before recreating
•   -C   Create database before restoring
•   -t   Dump the named table(s) only
•   -F   Format (c: custom, d: directory, t: tar) Use pg_dump -? to get the full list of options