The pgsql2pgsql tool supports migrating tables in AnalyticDB for PostgreSQL, Greenplum Database, PostgreSQL, or PPAS to AnalyticDB 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 AnalyticDB for PostgreSQL to PostgreSQL, PPAS, Greenplum Database, or AnalyticDB 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.
[src.pgsql] connect_string = "host=192.168.1.1 dbname=test port=3432 user=test password=pgsql"
The connection information of the local temporary PostgreSQL database is shown as follows:
[local.pgsql] connect_string = "host=192.168.1.2 dbname=test port=3432 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.
[desc.pgsql] connect_string = "host=192.168.1.3 dbname=test port=3432 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 stream replication, so it supports the incremental migration if PostgreSQL serves as the data source. The kernel only supports logic stream 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.
Status information query
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.