The pgsql2pgsql tool supports migrating tables in HybridDB for PostgreSQL, Greenplum Database, PostgreSQL, or PPAS to HybridDB for PostgreSQL, Greenplum Database, PostgreSQL, or PPAS without storing the data separately.
pgsql2pgsql supports the following features:
Full-database migration from PostgreSQL, PPAS, Greenplum Database, or HybridDB for PostgreSQL to PostgreSQL, PPAS, Greenplum Database, or HybridDB for PostgreSQL.
Full-database migration and incremental data migration from PostgreSQL or PPAS (9.4 or later versions) to PostgreSQL, or PPAS.
Modify the “my.cfg” configuration file, and configure the source and target database connection information.
The connection information of the source PostgreSQL database is shown as follows:
Note: The user is preferably the corresponding database owner in the source PostgreSQL database connection information.
connect_string = "host=192.168.1.1 dbname=test port=5888 user=test password=pgsql"
The connection information of the local temporary PostgreSQL database is shown as follows:
connect_string = "host=192.168.1.1 dbname=test port=5888 user=test2 password=pgsql"
The connection information of the target PostgreSQL database is shown as follows:
Note: You need to have the write permission on the target table of the target PostgreSQL database.
connect_string = "host=192.168.1.1 dbname=test port=5888 user=test3 password=pgsql"
- If you want to perform incremental data synchronization, the connected source database must have the permission to create replication slots.
- PostgreSQL 9.4 and later versions support logic flow replication, so it supports the incremental migration if PostgreSQL serves as the data source. The kernel only supports logic flow replication after you enable the following kernel parameters.
- wal_level = logical
- max_wal_senders = 6
- max_replication_slots = 6
Run the following command to perform a full-database migration:
By default, the migration program migrates the table data of all the users in the corresponding PostgreSQL database to PostgreSQL.
Connect to the local temporary database, and you can view the status information in a single migration process. The information is stored in the db_sync_status table, including the start and end time of the full-database migration, the start time of the incremental data migration, and the data situation of incremental synchronization.