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.
psql, the official postgres command-line client, is used for importing text-based dumps created by
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.
psqlare typically bundled with
Installing view brew (mac/osx):
brew install postgres
sudo apt install postgres
sudo dnf install postgresql
Download the official postgres installer from the official postgres docs and follow the provided prompts/instructions.
To ensure compatibility with bit.io, ensure
psqlare installed from postgres 14!
To check your installed
pg_dump version, run:
$ pg_dump --version pg_dump (PostgreSQL) 14.3
pg_dump to export data from bit.io in a plain-text format
pg_dumpto 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:
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
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).
pg_dumpwill export your database schemas in addition to all of its tables.
If you wish to export only data, and no schema information, append the
-aoption to your
pg_dumpcommand. 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
-soption to you
pg_dumpcommand. 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.
psql to import a plain-text database dump into a Postgres database
psqlto 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_restoreis only compatible with compressed
You MUST use
psqlto properly import plain-text backup files generated by
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:
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
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
pg_restore to import compressed database dumps into a postgres database
pg_restoreto 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.
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
Updated 8 months ago