Dump and Restore PostgreSQL Tables

Sometimes it's helpful to dump an exact copy of a database table so it can be loaded elsewhere. How can you do that?

In this example, we will dump and restore a table and assume there is no sensitive content in the table being dumped.

pg_dump can be used to dump table content to a file. By using compression the file size can be kept small even for large tables.

Specify the output format with --format (or -F). The c format is the default. Refer to pg_dump Docs for each of the options and values.

Without compression, the file would be 144MB file from a users table with 1042368 rows. With compression it is 19MB. Compression is continually being improved so this might be even better in newer versions of PostgreSQL or by using external compression programs.

In the example below, the database name is rideshare_development.

Run pg_dump as follows.

pg_dump \
    --format=c \
    --host localhost \
    --table users \
    rideshare_development > users_table.dump

$ du -h users_table.dump
19M    users_table.dump

From the .dump file, the table and all the data rows can be populated into a database where neither exists.

Create a database to create the table and populate it. Call the database temp_load. The command below uses the createdb command line program included with PostgreSQL. The next command loads the content of the dump file using pg_restore.

createdb temp_load;

pg_restore --dbname temp_load users_table.dump

-- Confirm all the rows are there
SELECT count(*) FROM users;
  count
---------
 1042368

That's it! Note that this is only one of the ways to dump and restore table rows. Explore the PostgreSQL documentation for more options.