dba-brewer
commented
6 years ago If anyone else runs into this and needs to mass-resync the sequences, I ran the following to get the sync statements:
select 'SELECT pglogical.synchronize_sequence(''' || relname || ''');' as sql
from pg_catalog.pg_statio_user_sequences
order by sql;Granted, the last_values will all be larger than the original by 1000, but I guess it's better than being smaller by 1000. Nobody wants to angry up a PK constraint, least of all on a Friday afternoon. :)
huayuanzzb
saurabhnanda
lknaubkonrad
PJMODOS
jjb
jstaf
pratmeht
rootwyrm
lpossamai
I am trying to use pglogical to migrate a Postgres 9.5 cluster running on FC24 to a new server running Postgres 9.6 on CentOS 7. It seems to be working “ok”, but there is an odd thing happening where the destination sequence’s last_value is the source’s last_value+1000. (e.g. source has last_value of 3, destination has last_value of 1003.)
Searching online I found someone else running into this issue ( https://groups.google.com/a/2ndquadrant.com/forum/#!topic/bdr-list/7y4zY09bLBA) and a developer at 2nd Quadrant asked for an issue to be opened in github, so here I am.
My process thus far is:
Source/Provider System
-bash-4.3$ dnf list installed | grep postg postgresql.x86_64 9.5.4-1.fc24 @updates postgresql-contrib.x86_64 9.5.4-1.fc24 @updates postgresql-libs.x86_64 9.5.4-1.fc24 @updates postgresql-pgpool-II.x86_64 3.5.2-1.fc24 @@commandline postgresql-pgpool-II-extensions.x86_64 postgresql-pgpoolAdmin.noarch 3.5.3-3.fc24 @@commandline postgresql-server.x86_64 9.5.4-1.fc24 @updates postgresql-upgrade.x86_64 9.5.4-1.fc24 @updates
Ran initdb to create the cluster and made the required changes to the postgresql.conf file:
Add entries for the destination IP for both local and replication connections to the pg_hba.conf file on source server:
Restart the postgres cluster on the source and destination servers.
Create the pglogical extension in the template1 database (so pglogical extension will automatically be created in all subsequent databases created):
On the source server, create the provider node. For example:
All tables being migrated are in the public schema, so add them to the default replication set:
Add all sequences to the default replication set as well:
Destination/Subscriber System
-bash-4.2$ yum list installed | grep postg postgresql.x86_64 9.2.18-1.el7 @base postgresql-contrib.x86_64 9.2.18-1.el7 @base postgresql-libs.x86_64 9.2.18-1.el7 @base postgresql95.x86_64 9.5.10-1PGDG.rhel7 @pgdg95 postgresql95-contrib.x86_64 9.5.10-1PGDG.rhel7 @pgdg95 postgresql95-libs.x86_64 9.5.10-1PGDG.rhel7 @pgdg95 postgresql95-pglogical.x86_64 2.1.1-1.el7 @pglogical postgresql95-server.x86_64 9.5.10-1PGDG.rhel7 @pgdg95
Ran initdb to create the cluster and made the required changes to the postgresql.conf file:
Add an entry for the destination IP (i.e. its own IP) to the pg_hba.conf file on destination server:
Restart the postgres cluster on the source and destination servers. Create the pglogical extension in the template1 database:
On the destination server, create the database you are migrating:
Create the subscriber node:
Create the subscription:
After executing this last step (create_subscription), the test database on the source server is replicated to the destination server. Mostly. If I look at the objects, the tables, views, indexes, sequences are there. However, users are not. This is expected since users are created globally and pglogical operates on individual databases. However, looking at the sequences, I see they exist, but their last_value differs from source to destination. For example:
Source test=# select * from users_id_seq; sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- users_id_seq | 3 | 1 | 1 | 9223372036854775807 | 1 | 1 | 32 | f | t (1 row)
Destination
test=# select * from users_id_seq; sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- users_id_seq | 1 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | t (1 row)
This happened even though I ran the replication_set_add_all_sequences on the source prior to creating the subscription. Also note, I set synchronize_data to true so the sequence values should have been synchronized immediately.
Ok, so try to manually synchronize them again. On the provider node, I ran the following:
Now when I check the last_value:
test=# select * from users_id_seq; sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- users_id_seq | 1003 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | t (1 row)
For some reason, it syncs the source value and then adds 1000 to it. I tried this on numerous sequences and the same thing happened. The destination’s last_value is set to source’s last_value+1000.