Importing an existing Postgres database
The simplest way to import a database into BigAnimal is using logical backups taken with pg_dump
and loaded using pg_restore
. This approach provides a way to export and import a database across different versions of Postgres, including exporting from PostgreSQL and EDB Postgres Advanced Server versions prior to 10.
The high-level steps are:
- Export existing roles.
- Import existing roles.
- For each database, you are migrating:
In case your source PostgreSQL instance hosts multiple databases, you can segment them in multiple BigAnimal clusters for easier management, better performance, increased predictability, and finer control of resources. For example, if your host has 10 databases, you can import one database and related users on a different BigAnimal cluster, one at a time.
Downtime considerations
This approach requires suspending write operations to the database application for the duration of the export/import process. You can then resume the write operations on the new system. This is because pg_dump
takes an online snapshot of the source database. As a result, the changes after the backup starts aren't included in the output.
The required downtime depends on many factors, including:
- Size of the database
- Speed of the network between the two systems
- Your team's familiarity with the migration procedure
To minimize the downtime, you can test the process as many times as needed before the actual migration. You can perform the export with pg_dump
online, and the process is repeatable and measurable.
Before you begin
Make sure that you:
- Understand the terminology conventions.
- Have the required Postgres client binaries and libraries.
- Can access the source and target databases.
Terminology conventions
Term | Alias | Description |
---|---|---|
source database | pg-source | Postgres instance from which you want to import your data. |
target database | pg-target | Postgres cluster in BigAnimal where you want to import your data. |
migration host | pg-migration | Temporary Linux machine in your trusted network from which to execute the export of the database and the subsequent import into BigAnimal. The migration host needs access to both the source and target databases. Or, if your source and target databases are on the same version of Postgres, the source host can serve as your migration host. |
Postgres client libraries
The following client binaries must be on the migration host:
pg_dumpall
pg_dump
pg_restore
psql
They must be the same version as the Postgres version of the target database. For example, if you want to import a PostgreSQL 10 database from your private network into a PostgreSQL 14 database in BigAnimal, use the client libraries and binaries from version 14.
Access to the source and target database
Access requirements:
- PostgreSQL superuser access to the source database. This can be the postgres user or another user with superuser privileges.
- Access to the target database in BigAnimal as the edb_admin user.
Verify your access
Connect to the source database using
psql
. For example:Replace
<pg-source>
with the actual hostname or IP address of the source database and theuser
anddbname
values as appropriate. If the connection doesn't work, contact your system and database administrators to make sure that you can access the source database. This might require changes to yourpg_hba.conf
and network settings. Ifpg_hba.conf
changes, reload the configuration with eitherSELECT pg_reload_conf();
using a psql connection orpg_ctl reload
in a shell connection to the database host.Connect to the target database using the edb_admin user. For example:
Replace
<pg-target>
with the actual hostname of your BigAnimal cluster.
Export existing roles
Export the existing roles from your source Postgres instance by running the following command on the migration host:
The generated SQL file looks like this:
Import the roles
- Your BigAnimal cluster already contains the
edb_admin
user, as well as the following-system required roles:
postgres
— The superuser, needed by BigAnimal to manage the cluster.streaming_replica