pg_dump Backups

This topic describes how to create a backup of your SerenDB database using the Postgres pg_dump utility and how to restore a backup using pg_restore.

Avoid using `pg_dump` over a [pooled connection string](/docs/reference/glossary#pooled-connection-string) (see PgBouncer issues [452](https://github.com/pgbouncer/pgbouncer/issues/452) & [976](https://github.com/pgbouncer/pgbouncer/issues/976) for details). Use an [unpooled connection string](/docs/reference/glossary#unpooled-connection-string) instead.

Prerequisites

• Make sure pg_dump and pg_restore are installed. You can verify by running pg_dump -V.

• We recommend using the latest versions of pg_dump and pg_restore, and ensuring that the client version matches your SerenDB project's Postgres version.

Install pg_dump and pg_restore

If you don't have the pg_dump and pg_restore utilities installed locally, you'll need to install them on your preferred platform.

<Tabs labels={["Windows", "Mac", "Linux", "Docker"]}>

1. Install PostgreSQL using the official installer from https://www.postgresql.org/download/windows/. 2. `pg_dump` and `pg_restore` are installed by default and can be found in the PostgreSQL `bin` directory. 1. Install PostgreSQL using Homebrew with the command: brew install postgresql. 2. `pg_dump` and `pg_restore` come with the installation and are available in your `PATH`. 1. On Ubuntu/Debian, install the PostgreSQL client tools with: `sudo apt-get install postgresql-client`. 2. `pg_dump` and `pg_restore` will be available after installation. 1. Pull the official PostgreSQL Docker image: `docker pull postgres`. 2. Run the container with: `docker run --name postgres -e POSTGRES_PASSWORD=yourpassword -d -p 5432:5432 postgres`. 3. Verify `pg_dump` is available by running: `docker run --rm postgres pg_dump --version`.

Creating a backup with pg_dump

Following this procedure will create a database backup locally, where you're running the pg_dump command.

1. Retrieve the connection string for your SerenDB database by navigating to your SerenDB Project Dashboard and clicking the Connect button to open the Connect to your database modal.

2. Deselect the Connection pooling option. You need a direct connection string, not a pooled one.

   Your connection string should look something like this:

   postgresql://alex:[e-mail]/neondb?sslmode=require&channel_binding=require

3. Create a backup of your SerenDB database by running the following pg_dump command with your SerenDB database connection string.

   pg_dump -Fc -v -d "<neon_database_connection_string>" -f <dump_file_name>

   After adding your SerenDB database connection string and a dump file name, your command will look something like this:

   pg_dump -Fc -v -d "postgresql://alex:[e-mail]/neondb?sslmode=require&channel_binding=require" -f mydatabase.bak

   The pg_dump command above includes these arguments:

   • -Fc: Sends the output to a custom-format archive suitable for input into pg_restore.

   • -v: Runs pg_dump in verbose mode, allowing you to monitor what happens during the dump operation.

   • -d: Specifies the connection string for your SerenDB database.

   • -f <dump_file_name>: The dump file name. It can be any name you choose (mydumpfile.bak, for example).

   For more command options, see Advanced pg_dump and pg_restore options.

Restoring a backup with pg_restore

This procedure shows how to restore a database using the pg_restore utility from a backup file created using pg_dump, as described above.

1. Create a new SerenDB project.

2. Create a database with the same name as the one you backed up. The pg_dump instructions above created a backup of a database named neondb. Your database name is likely different.

3. Retrieve the connection string for your SerenDB database:

   Go to your SerenDB project and click the Connect button to open the Connect to your database modal.

   Deselect the Connection pooling option. You need a direct connection string, not a pooled one.

   Your connection string should look something like this:

   postgresql://alex:[e-mail]/neondb?sslmode=require&channel_binding=require

4. Restore your data to the target database in SerenDB with pg_restore.

   pg_restore -v -d "<neon_database_connection_string>" <dump_file_name>

   After adding your SerenDB database connection string and the dump file name, your command will look something like this:

   pg_restore -v -d "postgresql://alex:[e-mail]/neondb?sslmode=require&channel_binding=require" mydatabase.bak

   The example above includes these arguments:

   • -v: Runs pg_restore in verbose mode, allowing you to monitor what happens during the restore operation.

   • -d: Specifies the SerenDB database to connect to. The value is a SerenDB database connection string. See Before you begin.

   • <dump_file_name> is the name of the dump file you created with pg_dump.

   For more command options, see Advanced pg_dump and pg_restore options.

pg_dump and pg_restore example

The following example shows how data is dumped from source database named neondb in one SerenDB project and restored to a neondb database in another SerenDB project using the commands described in the previous sections. (A database named neondb was created in the SerenDB project prior to running the restore operation.)

Before performing this procedure:

• A new SerenDB project was created for the destination database, and a database with the same name as the source database was created (neondb)

• Connection strings for the source and destination databases were collected:

  • source: postgresql://neondb_owner:[e-mail]/neondb?sslmode=require&channel_binding=require

  • destination: postgresql://neondb_owner:[e-mail]/neondb?sslmode=require&channel_binding=require

~$ cd mydump
~/mydump$ pg_dump -Fc -v -d "postgresql://alex:[e-mail]/neondb?sslmode=require&channel_binding=require" -f mydatabase.bak

~/mydump$ ls
mydatabase.bak

~/mydump$ pg_restore -v -d "postgresql://alex:[e-mail]/neondb?sslmode=require&channel_binding=require" mydatabase.bak