rtshome/pgrepup

Pgrepup is a tool for upgrading through PostgreSQL major versions using logical replication and pglogical extension

pgrepup - PostgreSQL REPlicate and UPgrade

pgrepup is a tool written in Python for upgrading a PostgreSQL cluster to a new major version using logical replication and pglogical extension.

pgrepup simplifies the setup of 2nd Quadrant's pglogical extension giving hints for configuring correctly source and destination pgsql clusters.

The supported versions of PostgreSQL are 9.4, 9.5, 9.6 and 10.

Quick start

Requirements

pgrepup requires both a source and a destination PostgreSQL cluster.

The clusters can have been installed into the same server or on different hosts.

pgrepup doesn't need to be installed into the clusters' server. It can be safely executed from a remote host that can access both pgsql clusters. In this case, it's recommended that SSL is enabled in pg_hba.conf of both clusters because pgsql credentials are sent over the network.

Installation

pip install pgrepup

All versions of Python >= 2.7 are supported.

Replication

A pgsql cluster can be replicated and upgraded through these four steps:

1. pgrepup config: a simple wizard asks the basic configuration parameters needed by pgrepup
   • Source and Destination database cluster
   • Directory where to store temporary files
2. pgrepup check: various checks are done both in Source and Destination cluster
   • if a check fails, pgrepup outputs a hint for helping you to configure each cluster
3. pgrepup setup: if the checks are all ok, this setup installs and configure pglogical in both pgsql clusters
4. pgrepup start: start the replication process

After the start command, you can monitor the replication process using the command pgrepup status.

The output of the status command displays an entry for each database of the source cluster along with the status reported by pglogical extension. The status can be one of the following three values:

• initializing: pglogical is copying data from source to destination cluster
• replicating: pglogical is using pgsql logical replication to replicate and upgrade new data changed into the source cluster
• down: replication is down, check the PostgreSQL log in both clusters

After a while where the databases are all in initializing status, each database status will change to replicating as the data is progressively copied from the source cluster.

Upgrade

When the replication is working fine, you can switch your application to the Destination cluster at any moment. Just follow these simple steps:

• stop your application connecting to the source cluster
• ensure no more connections are made to the source cluster
• stop replication using pgrepup stop command
• change the DSN in your application (or in your connection pooler) and point to the destination cluster
• start your application
• upgrade done! :-)

Uninstall

pglogical and others settings applied by pgrepup can be removed at any time using the command:

pgrepup uninstall

Caveats

pgrepup is still experimental. Please feel free to open an issue on github if you encounter problems.

DDL commands

DDL commands issued in a source cluster database are not replicated to the destination cluster. This is a limit of how pgsql logical replication works. Use the `pglogical.replicate_ddl_command SQL function on the source database in order to replicate the DDL on the destination cluster.

Be aware that, at the moment, pgrepup doesn't handle the automatic subscription of newly created tables added using pglogical.replicate_ddl_command . The recommended procedure is to re-start the replication process using the stop, setup and start commands. `` A solution is in the works and will be available in the next release of pgrepup.

Sequences

Sequences are replicated between source and destination cluster. When the stop command is given, pgrepup uses pglogical function to do a final synchronization of each sequence value. The pglogical function adds an artificial +1000 value to the actual sequence value: see this discussion on pglogical mailing list on google groups

High number of databases

pgrepup has been tested with success to replicate several clusters different both in size and database number.

However, after issuing a start command, pglogical background workers start all simultaneously to dump the data of the source database into the destination database.

This can generate very high cpu/disk load on both clusters depending on the number of databases to replicate.

A feature that enables to limit the number of databases that are dumped concurrently is in the works.

License and contributions

pgrepup is licensed using GPL-3 license. Contributions are welcome!

pgrepup's Issues

Unable to setup the source node when there are tables without primary keys

pglogical doesn't correctly allow to add tables without a primary key when replicating both inserts, updates and deletes as it cannot identify the records to modify in the latter two cases.

When creating the replication set, pglogical outputs the following message:

ERROR: table <table_name> cannot be added to replication set default
DETAIL: table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs
HINT: Add a PRIMARY KEY to the table

I think we should check for tables missing the primary key (or unique index) already in the check command and give hints to fix the issue

Color main titles of operations like did in config command

Different pg_dumpall version

pglogical suppose to handle different pg versions.

I have instances with source 9.5.6 and destination with 9.6.2.

When i'm trying pgrepup check
I'm receiving

Pgrepup 0.3.7
Global checkings...
 >  Folder /tmp exists and is writable ...........................................OK
Checking Source...
 >  Connection PostgreSQL connection to 10.240.0.37:5432 with user postgres ......OK
 >  pglogical installation .......................................................OK
 >  Needed wal_level setting .....................................................OK
 >  Needed max_worker_processes setting ..........................................OK
 >  Needed max_replication_slots setting .........................................OK
 >  Needed max_wal_senders setting ...............................................OK
 >  pg_hba.conf settings .........................................................OK
 >  Local pg_dumpall version .....................................................KO
    Hint: Upgrade local PostgreSQL client utils to version 9.6.2
 >  Source cluster tables without primary keys
 >      template1 ................................................................OK
 >      postgres
 >          public.scraped_results ...............................................OK
 >          public.base_data .....................................................OK
 >          public.scraped_results_archive .......................................OK
 >          public.data_changes ..................................................OK
 >          public.alembic_version ...............................................OK
 >      nicknames
 >          public.nicknames .....................................................OK
 >          public.alembic_version ...............................................OK
 >      scorrero
 >          public.score_old .....................................................OK
 >          public.score .........................................................OK
 >          public.alembic_version ...............................................OK
 >      scraping
 >          public.scraped_results ...............................................OK
 >          public.scraped_results_archive .......................................OK
 >          public.alembic_version ...............................................OK
 >      estimato
 >          public.estimates .....................................................OK
 >          public.alembic_version ...............................................OK
 >      prospectre
 >          public.alembic_version ...............................................OK
 >          public.celery_taskmeta ...............................................OK
 >          public.scored_target_prospects .......................................OK
 >          public.organization_target_status ....................................OK
 >          public.celery_tasksetmeta ............................................OK
 >      test
 >          public.data_changes ..................................................OK
 >          public.base_data .....................................................OK
 >      email-finder
 >          public.model_sync_status .............................................OK
 >          public.alembic_version ...............................................OK
 >          public.fixed_email_patterns ..........................................OK
 >          public.email2 ........................................................OK
 >          public.metrics_email2 ................................................OK
 >          public.analytics_email2 ..............................................OK
 >          public.catch_all_domains .............................................OK
 >          public.event_log .....................................................OK
Checking Destination...
 >  Connection PostgreSQL connection to 10.240.0.54:5432 with user postgres ......OK
 >  pglogical installation .......................................................OK
 >  Needed wal_level setting .....................................................OK
 >  Needed max_worker_processes setting ..........................................OK
 >  Needed max_replication_slots setting .........................................OK
 >  Needed max_wal_senders setting ...............................................OK
 >  pg_hba.conf settings .........................................................OK
 >  Local pg_dumpall version .....................................................KO
    Hint: Upgrade local PostgreSQL client utils to version 9.6.2

Would You be so kind and take a look at that?

Maybe it is similar to #7

pglogical installation is not correctly detected

max_replication_slots is not correctly checked in destination cluster

When a hostname is given as src/dest address the hint for the pg_hba.conf is invalid

Handle exceptions on pg_dumpall check failure

valid?

Is this repo valid anymore?

Errors when 0.3.10 call stop command

  pgsql-proxy: ++ pgrepup -c /etc/pgrepup/pgrepup_pgsql1.conf stop
  pgsql-proxy: Pgrepup 0.3.10
  pgsql-proxy: Traceback (most recent call last):
  pgsql-proxy:   File "/usr/local/bin/pgrepup", line 25, in <module>
  pgsql-proxy:
  pgsql-proxy: main()
  pgsql-proxy:   File "/usr/local/lib/python2.7/dist-packages/pgrepup/cli.py", line 64, in main
  pgsql-proxy:
  pgsql-proxy: dispatch(__doc__)
  pgsql-proxy:   File "/usr/local/lib/python2.7/dist-packages/pgrepup/helpers/docopt_dispatch.py", line 39, in __call__
  pgsql-proxy:
  pgsql-proxy: function(**self._kwargify(arguments))
  pgsql-proxy: TypeError
  pgsql-proxy: :
  pgsql-proxy: stop() takes no arguments (14 given)

pgrepup fix suggestion

I had no primary key on some tables.
So pgrepup fix fixed it by adding another column with __pgrepup_id__ or some similar name.

Thats really a not very good idea to alter DB's without explaining to user, what is going to be altered.
In my case, this alteration would broke our deploy process.

What did helped me, id adding a primary key to the tables itself.

ALTER TABLE alembic_version ADD PRIMARY KEY (version_num);

That helped.

On Debian Jessie the password encryption does not work

pgrepup config with one DB

Hi again.

I have successfully started replication from pg 9.5.6 to pg 9.6.2 but:

In pgrepup config step, i've chosen only one DB to replicate.

cat .pgrepup

[Security]
encrypted_credentials = n
tmp_folder = /tmp
pg_repup_user_password = eaf01f67a45d4853b38340fc9cc137af

[Source]
host = 10.240.0.2
port = 5432
connect_database = scraping
user = postgres
password = ssap

[Destination]
host = 10.240.0.80
port = 5432
connect_database = scraping
user = postgres
password = ssap

Instead of replicating only scraping db, all databases from master node are being replicated to slave node.

And now i'm trying to perform 'pgrepup stop', and it's not happening.

pgrepup stop

Pgrepup 0.3.7
Check active subscriptions in Destination nodes
 >  template1 ................................................................Active
 >      Launch stop command ..................................................OK
 >  postgres .................................................................Active
 >      Launch stop command ..................................................OK
 >  nicknames ................................................................Active
 >      Launch stop command ..................................................OK
 >  scorrero .................................................................Active

It hangs on 170GB database, and still replicating other databases.

Including logs from slave node

2017-04-06 08:54:12.943 UTC [1981] LOG:  starting pglogical database manager for database postgres
2017-04-06 08:54:13.008 UTC [1983] LOG:  starting pglogical database manager for database scraping
2017-04-06 08:54:13.076 UTC [1985] LOG:  starting pglogical database manager for database email-finder
2017-04-06 08:54:13.140 UTC [1987] LOG:  starting pglogical database manager for database estimato
2017-04-06 08:54:13.210 UTC [1989] LOG:  starting pglogical database manager for database nicknames
2017-04-06 08:54:13.276 UTC [1992] LOG:  starting pglogical database manager for database prospectre
2017-04-06 08:54:13.352 UTC [1994] LOG:  starting pglogical database manager for database scorrero
2017-04-06 08:54:13.419 UTC [1996] LOG:  starting pglogical database manager for database template1
2017-04-06 08:55:17.747 UTC [2059] LOG:  starting apply for subscription subscription
2017-04-06 08:55:17.781 UTC [2062] LOG:  starting apply for subscription subscription
2017-04-06 08:55:17.825 UTC [2066] LOG:  starting apply for subscription subscription
2017-04-06 08:55:17.870 UTC [2070] LOG:  starting apply for subscription subscription
2017-04-06 08:55:17.943 UTC [2075] LOG:  starting apply for subscription subscription
2017-04-06 08:55:18.098 UTC [2079] LOG:  starting apply for subscription subscription
2017-04-06 08:55:18.210 UTC [2083] LOG:  starting apply for subscription subscription
2017-04-06 08:55:18.321 UTC [2087] LOG:  starting apply for subscription subscription
2017-04-06 08:55:29.136 UTC [2095] postgres@postgres ERROR:  existing subscription "subscription" to node "Source" already subscribes to replication set "default"
2017-04-06 08:55:29.136 UTC [2095] postgres@postgres STATEMENT:
                    SELECT pglogical.create_subscription(
                                            subscription_name := 'subscription',
                                            provider_dsn := 'host=10.240.0.2 port=5432 dbname=postgres user=pgrepup_replication password=eaf01f67a45d4853b38340fc9cc137af',
                                            replication_sets := '{default}'::text[]
                    );

2017-04-06 08:55:29.212 UTC [2097] postgres@scraping ERROR:  existing subscription "subscription" to node "Source" already subscribes to replication set "default"
2017-04-06 08:55:29.212 UTC [2097] postgres@scraping STATEMENT:
                    SELECT pglogical.create_subscription(
                                            subscription_name := 'subscription',
                                            provider_dsn := 'host=10.240.0.2 port=5432 dbname=scraping user=pgrepup_replication password=eaf01f67a45d4853b38340fc9cc137af',
                                            replication_sets := '{default}'::text[]
                    );

2017-04-06 08:55:29.246 UTC [2099] postgres@email-finder ERROR:  existing subscription "subscription" to node "Source" already subscribes to replication set "default"
2017-04-06 08:55:29.246 UTC [2099] postgres@email-finder STATEMENT:
                    SELECT pglogical.create_subscription(
                                            subscription_name := 'subscription',
                                            provider_dsn := 'host=10.240.0.2 port=5432 dbname=email-finder user=pgrepup_replication password=eaf01f67a45d4853b38340fc9cc137af',
                                            replication_sets := '{default}'::text[]
                    );

2017-04-06 08:55:29.273 UTC [2101] postgres@estimato ERROR:  existing subscription "subscription" to node "Source" already subscribes to replication set "default"
2017-04-06 08:55:29.273 UTC [2101] postgres@estimato STATEMENT:
                    SELECT pglogical.create_subscription(
                                            subscription_name := 'subscription',
                                            provider_dsn := 'host=10.240.0.2 port=5432 dbname=estimato user=pgrepup_replication password=eaf01f67a45d4853b38340fc9cc137af',
                                            replication_sets := '{default}'::text[]
                    );

2017-04-06 08:55:29.301 UTC [2103] postgres@nicknames ERROR:  existing subscription "subscription" to node "Source" already subscribes to replication set "default"
2017-04-06 08:55:29.301 UTC [2103] postgres@nicknames STATEMENT:
                    SELECT pglogical.create_subscription(
                                            subscription_name := 'subscription',
                                            provider_dsn := 'host=10.240.0.2 port=5432 dbname=nicknames user=pgrepup_replication password=eaf01f67a45d4853b38340fc9cc137af',
                                            replication_sets := '{default}'::text[]
                    );

2017-04-06 08:55:29.327 UTC [2105] postgres@prospectre ERROR:  existing subscription "subscription" to node "Source" already subscribes to replication set "default"
2017-04-06 08:55:29.327 UTC [2105] postgres@prospectre STATEMENT:
                    SELECT pglogical.create_subscription(
                                            subscription_name := 'subscription',
                                            provider_dsn := 'host=10.240.0.2 port=5432 dbname=prospectre user=pgrepup_replication password=eaf01f67a45d4853b38340fc9cc137af',
                                            replication_sets := '{default}'::text[]
                    );

2017-04-06 08:55:29.352 UTC [2107] postgres@scorrero ERROR:  existing subscription "subscription" to node "Source" already subscribes to replication set "default"
2017-04-06 08:55:29.352 UTC [2107] postgres@scorrero STATEMENT:
                    SELECT pglogical.create_subscription(
                                            subscription_name := 'subscription',
                                            provider_dsn := 'host=10.240.0.2 port=5432 dbname=scorrero user=pgrepup_replication password=eaf01f67a45d4853b38340fc9cc137af',
                                            replication_sets := '{default}'::text[]
                    );

2017-04-06 08:55:29.383 UTC [2109] postgres@template1 ERROR:  existing subscription "subscription" to node "Source" already subscribes to replication set "default"
2017-04-06 08:55:29.383 UTC [2109] postgres@template1 STATEMENT:
                    SELECT pglogical.create_subscription(
                                            subscription_name := 'subscription',
                                            provider_dsn := 'host=10.240.0.2 port=5432 dbname=template1 user=pgrepup_replication password=eaf01f67a45d4853b38340fc9cc137af',
                                            replication_sets := '{default}'::text[]
                    );

2017-04-06 09:35:35.272 UTC [2087] LOG:  apply worker [2087] at slot 15 generation 1 detaching cleanly
2017-04-06 09:35:38.412 UTC [2059] LOG:  apply worker [2059] at slot 8 generation 1 detaching cleanly
2017-04-06 09:35:41.278 UTC [2075] LOG:  apply worker [2075] at slot 12 generation 1 detaching cleanly
2017-04-06 09:57:11.145 UTC [8767] postgres@postgres ERROR:  subscriber subscription not found
2017-04-06 09:57:11.145 UTC [8767] postgres@postgres STATEMENT:  SELECT status FROM pglogical.show_subscription_status(subscription_name := 'subscription');
2017-04-06 09:57:11.613 UTC [8771] postgres@nicknames ERROR:  subscriber subscription not found
2017-04-06 09:57:11.613 UTC [8771] postgres@nicknames STATEMENT:  SELECT status FROM pglogical.show_subscription_status(subscription_name := 'subscription');
2017-04-06 09:57:12.374 UTC [8774] postgres@template1 ERROR:  subscriber subscription not found
2017-04-06 09:57:12.374 UTC [8774] postgres@template1 STATEMENT:  SELECT status FROM pglogical.show_subscription_status(subscription_name := 'subscription');

start command fails with python exception if pgrepup has not been installed

The exception is:

Traceback (most recent call last):
File "./pgrepup.exe", line 25, in
main()
File "pgrepup/pgrepup/cli.py", line 47, in main
dispatch(doc)
File "pgrepup/pgrepup/helpers/docopt_dispatch.py", line 39, in call
function(**self._kwargify(arguments))
File "pgrepup/pgrepup/commands/start.py", line 35, in start
start_subscription(d)
File "pgrepup/pgrepup/helpers/replication.py", line 99, in start_subscription
[get_dsn_for_pglogical('Source', db)]
psycopg2.ProgrammingError: schema "pglogical" does not exist
LINE 2: SELECT pglogical.create_subscription(
^

Handle exceptions when trying to connect to PostgreSQL clusters

Can't pass pgrepup check on PostgreSQL 9.4 as pgl_ddl_deploy does not support PostgreSQL 9.4

Is it possible to skip pgl_ddl_deploy usage in this case?

Error with db name

If db name contains "-", we get an error

To fix this, edit
vim pgrepup/lib/python2.7/site-packages/pgrepup/helpers/replication.py

find
c.execute('GRANT CREATE ON DATABASE ' + db + ' TO ' + user)
and replace it with
c.execute('GRANT CREATE ON DATABASE ' + '"' + db + '"' + ' TO ' + user)