Export and Restore Data with pg_dump, pg_restore, and psql

Exporting data from bit.io may be useful if you wish to use your data offline in a disconnected environment, would like to make your own data backup, or would simply like to migrate your database from bit.io into a separate database platform (locally or otherwise).

Import data to bit.io may be useful if you wish to migrate from a prior database system to bit.io, or if you wish to copy data easily between bit.io databases.

pg_dump is an official postgres command-line based tool for exporting data from any postgres-compliant database, including bit.io.

Typically, psql, the official postgres command-line client, is used for importing text-based dumps created by pg_dump.

pg_restore is effectively pg_dump but operates in the reverse direction. pg_restore is used for importing any compressed database dumps created by pg_dump into any postgres-compliant database, including bit.io.

Installing pg_dump, pg_restore, and psql

📘

pg_dump , pg_restore, and psql are typically bundled with postgres installations.

Installing view brew (mac/osx):

brew install postgres

Installing via apt (debian/ubuntu):

sudo apt install postgres

Installing via dnf (fedora/RHEL):

sudo dnf install postgresql

Windows users:

Download the official postgres installer from the official postgres docs and follow the provided prompts/instructions.

🚧

To ensure compatibility with bit.io, ensure pg_dump, pg_restore, and psql are installed from postgres 14!

To check your installed pg_dump version, run:

$ pg_dump --version
pg_dump (PostgreSQL) 14.3

Using pg_dump to export data from bit.io in a plain-text format

First, locate your database connection string from the bit.io web console for the database you wish to export and copy it:

Next, invoke pg_dump from you machine with the copied postgres connection string. Using the -f option to specify an output destination:

pg_dump -f my-dump.sql postgresql://my-username:[email protected]/my-username/my-database

By default, pg_dump will export your database into a plain-text .sql file. This file will included all of the statements necessary to recreate your bit.io database from scratch.

You can view the text backup file in any text editor to ensure it is accurate and complete (but be careful doing so with large databases).

📘

By default, pg_dump will export your database schemas in addition to all of its tables.

If you wish to export only data, and no schema information, append the -a option to your pg_dump command. This is useful if the tables you are trying to export already exist in your target database.

Likewise if you wish to export only the schema of your database, and no data, append the -s option to you pg_dump command. This is useful if you wish to pre-populate your target database with your table definitions, but don't want to actually import any pre-existing data.

Using psql to import a plain-text database dump into a Postgres database

In this example we will demonstrate how to use psql to load a .sql backup file generated by pg_dump into a locally running postgres database.

🚧

pg_restore is only compatible with compressed pg_dump files.

You MUST use psql to properly import plain-text backup files generated by pg_dump.

First, ensure you have all of the details necessary to connect to your target database. You will either need a full postgres connection string (such as the one you use to connect to bit.io), or individual connection credentials, such as username, password, hostname, and port number.

Next, supply your connection details and backup file to psql:

psql postgresql://my-username:[email protected]/my-username/my-database < my-dump.sql

If you are connecting to your target import database without a connection string, use the following psql syntax. In this example, psql is connecting as the user my-username to a postgres server running on localhost on port 5432:

psql -h 127.0.0.1 -p 5342 -u my-username < my-dump.sql

If a password is required, you can specify if via the PGPASSWORD environment variable:

PGPASSWORD=my-password psql -h 127.0.0.1 -p 5342 -u my-username < my-dump.sql

If you would like to import your database backup into a bit.io database, simply grab your bit.io connection string from the bit.io web console as shown above, and supply that when connecting to psql:

psql postgresql://my-username:[email protected]/my-username/my-database < my-dump.sql

Using pg_restore to import compressed database dumps into a postgres database

If you created a compressed database dump using the -c (custom compressed format) or -t (tarball compressed format) pg_dump options, you MUST use pg_restore to import your database dump into your target database.

📘

Compressed database dump files take up less disk space and import faster than text based files

You can determine which database dump format to use depending on the size of your database. For larger databases, using a compressed format is highly reccomended.

This example pg_restore command imports the provided compressed custom format backup to the given target database:

pg_restore -d postgresql://my-username:[email protected]/my-username/my-database my-database-dump-file