rtshome / pgrepup Goto Github PK

View Code? Open in Web Editor NEW

37.0 37.0 9.0 126 KB

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

License: GNU General Public License v3.0

Python 100.00%

pgrepup's People

Contributors

Stargazers

Watchers

Forkers

slach maxout123 fulhack kirovilya tatikoma neonsludge guiriduro mehmettopcu

pgrepup's Issues

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

max_replication_slots is not correctly checked in destination cluster

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)

Handle exceptions when trying to connect to PostgreSQL clusters

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

On Debian Jessie the password encryption does not work

valid?

Is this repo valid anymore?

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

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)

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?

pglogical installation is not correctly detected

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(
^

Color main titles of operations like did in config command

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');

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.