Backup and Restore PostgreSQL Tables from the Command Line

If you need to backup and restore data for a single table in PostgreSQL you can use the following methods:

Backup table data

pg_dump -Fc --data-only -h<host> -p<port> -U<username> -W -d<database> -t<table> > ~/Downloads/<filename.dump>

Restore table data

Before you restore you need to connect to the database and truncate the table to clear all the data:

1. psql -h<host> -p<port> -U<username> -W -d<database>
2. TRUNCATE <table> RESTART IDENTITY;
3. \q

That will connect to the database, truncate the table and then disconnect again. After which you can import the data into the empty table:

pg_restore --data-only -h<host> -p<port> -U<username> -W -d<database> -t<table> ~/Downloads/<filename.dump>

Explanation

• -Fc outputs to custom format, suitable for use with pg_restore
• --data-only dumps only the data, not the schema
• -h<host> specify the host you're connecting to
• -p<port> specify the port you're connecting to
• -U<username> specify the database user
• -W prompt for password
• -d<database> the database you're exporting from
• -t<table> the table you're exporting from