The pg_dump command can be used to backup a Postgres databases.
The psql command with the --list flag can be used to display the Postgres databases. Something like this should be returned.
~]$ psql --list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
foo | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
bar | john.doe | UTF8 | en_US.utf8 | en_US.utf8 |
In this example, the foo database is backed up to a file named foo.backup.txt. Yes, this is just a text file. In this scenario, the psql command would need to be used to restore the database.
pg_dump foo > /path/to/foo.backup.txt
Or the --format custom option can be used so that the pg_restore command can be used to restore the database.
pg_dump foo --format custom > /path/to/foo.backup.sql
You may want to include the --blobs flag to include large files in the backup.
pg_dump foo --format custom --blobs > /path/to/foo.backup.sql
The --format tar option can be used to create a backup in a tar archive.
pg_dump foo --format tar > /path/to/foo.backup.sql
The --port option can be used if Postgres is listening on a port other than 5432.
pg_dump foo --port 12345 > /path/to/foo.backup.sql
gzip can be used to compress the backup file.
pg_dump foo --format custom | gzip > /path/to/foo.backup.sql
The -U or --username option can be used to include a username.
pg_dump --username john.doe foo > /path/to/foo.backup.txt
The psql --dbname command can be used to list the tables in a database.
~]# psql --dbname foo
~]# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------------------+-------+-------
public | table001 | table | john.doe
public | table002 | table | john.doe
public | table003 | table | john.doe
(3 rows)
The \q (quit) command is then used to return to the prior prompt.
~]# \q
The -t or --table option can be used to backup a single table in a database. In this example, only table001 will be backed up.
pg_dump foo --table=table001 > /path/to/table001.backup.txt
Did you find this article helpful?
If so, consider buying me a coffee over at