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