Table of Contents Previous Next


13 Database Management : 13.2 Moving an Existing Database into a New Cluster

You can use the Postgres pg_dump utility to migrate an existing Postgres database (schema, data, and associated database objects) into an EDB Ark cluster.
pg_dump creates an archive that contains the commands needed to re-create and populate your existing database. After moving the archive to the master node of the Ark cluster, use pg_restore to uncompress and play the SQL commands contained in the archive. The following section will walk you through the process of moving a database to EDB Ark using pg_dump.
You can also use the pg_dumpall utility to move an entire Postgres cluster (data, schema information, and roles) to EDB Ark; for detailed information about using pg_dumpall, please see the Postgres documentation at:
Use the pg_dump utility to create an archive that contains the commands required to recreate a database. When invoking pg_dump, include the -Ft flag to instruct pg_dump to format the output as a tar file, and the -U flag to specify the name of the database superuser (see Figure 13.4):
pg_dump -Ft -U db_superuser db_name > archive_name.tar
C:\Users\susan\Desktop\Screen shot 2015-03-09 at 1.56.50 PM.png
db_superuser is the name of a Postgres database superuser.
db_name is the name of the database that you wish to move to EDB Ark.
archive_name.tar is the complete path and name of the archive. Please note that you must have permission to write a file to the location specified.
Use the scp command to copy the archive to the master server in the EDB Ark cluster; include the -i option to specify the location of your ssh key (see Figure 13.5):
scp -i ssh_key_file file_name user_name@host_name:target
C:\Users\susan\Desktop\Screen shot 2015-03-09 at 2.02.37 PM.png
ssh_key_file specifies the pathname of the EDB Ark ssh private key file.
file_name specifies the archive name.
user_name specifies the name used to connect to the master node of the EDB Ark cluster.
host_name specifies the host name of the master node of the EDB Ark cluster; the host name is located on the Details panel of the Clusters tab in the Ark console.
target specifies the name of the target directory on the EDB Ark host. Including :/tmp/ at the end of this command directs scp to copy the file to the tmp directory
Use ssh to connect to your EDB Ark cluster master node. Provide the user identity of the operating system superuser, and the location of the ssh key (on your local host) in the command (see Figure 13.6):
ssh -i/path/ssh_key.pem root@host_name
path specifies the location of your EDB Ark ssh certificate on the system from which you are connecting.
ssh_key.pem specifies the name of the EDB Ark ssh private key file.
host_name specifies the host name of the master node of the EDB Ark cluster; the host name is located on the Details panel of the Clusters tab in the Ark console.
C:\Users\susan\Desktop\Screen shot 2015-03-09 at 2.06.18 PM.png
C:\Users\susan\Desktop\Screen shot 2015-03-09 at 2.10.34 PM.png
Before invoking the pg_restore utility, you must create the target database in the master server; you can use the createdb client utility at the command line to create the target:
createdb -U db_superuser database_name
db_superuser specifies the name of the database superuser. On an Advanced Server cluster, the default is enterprisedb; on a PostgreSQL cluster, the default is postgres.
database_name specifies the name of the database on EDB Ark.
Then, invoke the pg_restore utility:
pg_restore -Ft -U db_superuser /path/archive_name.tar -d target_db_name
db_superuser specifies the name of the database superuser. On an Advanced Server cluster, the default is enterprisedb; on a PostgreSQL cluster, the default is postgres.
path is the pathname to the archive on the Ark.
archive_name.tar is the name of the archived database.
target_db_name is the name of the target database on the Ark.
the -Ft flag to specify that the file is an archive
the -U flag to specify the name of a database superuser.
the -d target_db_name flag to specify the name of the target database
C:\Users\susan\Desktop\Screen shot 2015-03-09 at 2.15.48 PM.png
After performing the restore, you can use the psql client to connect to the EDB Ark and confirm that the database has been transferred (see Figure 13.9):
psql -U database_superuser -d target_db_name
db_superuser specifies the name of the database superuser. On an Advanced Server cluster, the default is enterprisedb; on a PostgreSQL cluster, the default is postgres.
target_db_name is the name of the target database.
Use the \dt command to view a list of database objects in the current database:
C:\Users\susan\Desktop\Screen shot 2015-03-09 at 2.44.41 PM.png
To exit the psql client, enter \q; to exit the ssh session, type exit and Return.
For more information about using the psql client, please see the tutorial, Connecting to an EDB Ark. You can access the tutorial through the Dashboard tab of the Ark console.
Migration Toolkit is a powerful command-line tool that offers granular control of the migration process. Migration Toolkit can migrate immediately and directly into a Postgres database (online migration), or you can also choose to generate scripts to use at a later time to recreate object definitions in a Postgres database (offline migration).
If you are only migrating schema objects to a cluster, and use an ssh tunnel (with compression enabled), online migration of database object definitions may be a viable option. If you are migrating large amounts of data, network overhead may make an online migration prohibitively slow; Migration Toolkit's ‑offlineMigration option might provide a better migration path.

13 Database Management : 13.2 Moving an Existing Database into a New Cluster

Table of Contents Previous Next