Bootstrap FreeKB - Postgres (SQL) - Backup a Postgres database using the pg_dump command
Postgres (SQL) - Backup a Postgres database using the pg_dump command

Updated:   |  Postgres (SQL) articles

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 Buy Me A Coffee



Comments


Add a Comment


Please enter 85baa4 in the box below so that we can be sure you are a human.