[Bucardo-general] Miscellaneous number of rows with bucardo 5.4.1 logical replication in the table where there are child tables

[Пацев Антон]

Hello Greg!

My way sync using bucardo:


*On PostgreSQL 9.3:*

[root at server-postgres-9.3 bucardo-updated]# ./replica_add.sh
--------------------------------------------------
DBNAME - my_database
--------------------------------------------------
Added database "my_database_node1"
Added database "my_database_node2"
Creating relgroup: my_database_herd
Added table public.my_table_2017_05_12 to relgroup my_database_herd

New tables added: 22
Added sequence public.sequence_id_seq to relgroup my_database_herd

New sequences added: 6
Created dbgroup "my_database_servers_group"
Added database "my_database_node1" to dbgroup "my_database_servers_group"
as source
Added database "my_database_node2" to dbgroup "my_database_servers_group"
as target
--------------------------------------------------
done
[root at server-postgres-9.3 bucardo-updated]# su - postgres
-bash-4.1$ ls
9.3  my_database.sql.tar
-bash-4.1$ mc

-bash-4.1$ pg_dump -Fc my_database > my_database.sql.tar
-bash-4.1$ scp ./my_database.sql.tar apatsev at server-postgres-9.5:~
apatsev at server-postgres-9.5's password:
my_database.sql.tar

            100%   19MB  18.8MB/s   00:00
-bash-4.1$ logout
[root at server-postgres-9.3 bucardo-updated]# ./replica_sync_add.sh
--------------------------------------------------
DBNAME - my_database
--------------------------------------------------
Added sync "my_database_sync"
--------------------------------------------------
done

[root at server-postgres-9.3 bucardo-updated]# bucardo start
Checking for existing processes
Starting Bucardo
[root at server-postgres-9.3 bucardo-updated]# bucardo status
PID of Bucardo MCP: 23049
 Name                       State
  Last good    Time    Last I/D    Last bad    Time
==========================+=================================================+============+=======+===========+===========+=======
 my_database_sync   | DELETE public.my_table_2017_05_14 (KID 23088) | none
      |       |           | none      |

[root at server-postgres-9.3 bucardo-updated]# cd /; sudo -u postgres psql

psql (9.3.5)
Type "help" for help.

[local]:5432 postgres at postgres # \c my_database
You are now connected to database "my_database" as user "postgres".
[local]:5432 postgres at my_database # SELECT count(*) from my_table;
 count
--------
 677484
(1 row)

[local]:5432 postgres at my_database # SELECT count(*) from my_table;
 count
--------
 677545
(1 row)

[local]:5432 postgres at my_database # \q
You have new mail in /var/spool/mail/root
[root at server-postgres-9.3 /]# bucardo status
PID of Bucardo MCP: 23049
 Name                       State    Last good    Time    Last I/D    Last
bad    Time
==========================+========+============+=======+===========+===========+=======
 my_database_sync   | Good   | 10:15:24   | 12s   | 0/86      | none      |


[root at server-postgres-9.3 /]# cd /; sudo -u postgres psql

psql (9.3.5)

[local]:5432 postgres at postgres # \c my_database
You are now connected to database "my_database" as user "postgres".
[local]:5432 postgres at my_database # SELECT count(*) from my_table;
 count
--------
 677545
(1 row)


[local]:5432 postgres at my_database #

*On PostgreSQL 9.5:*

 ➤ psql://postgres@server-postgres-9.5:5432/postgres

# DROP DATABASE my_database;
DROP DATABASE
Time: 196.678 ms

 ➤ psql://postgres@server-postgres-9.5:5432/postgres

# create database my_database with owner my_database;
CREATE DATABASE
Time: 864.592 ms

 ➤ psql://postgres@server-postgres-9.5:5432/postgres

# \q
[root at server-postgres-9.5 /]# mc

[root at server-postgres-9.5 pgsql]# su - postgres

-bash-4.2$ ls
9.5  my_database.sql.tar
-bash-4.2$ pg_restore -d my_database my_database.sql.tar


[root at server-postgres-9.5 pgsql]# cd /; sudo -u postgres psql

psql (9.5.7)
Type "help" for help.

# \c my_database
You are now connected to database "my_database" as user "postgres".

 ➤ psql://postgres@server-postgres-9.5:5432/my_database

# SELECT count(*) from my_table;
 count
--------
 951939
(1 row)

 ➤ psql://postgres@server-postgres-9.5:5432/my_database

# ALTER TABLE my_table ENABLE ALWAYS TRIGGER
trigger_create_table_partition_and_insert_function;
ERROR:  trigger "trigger_create_table_partition_and_insert_function" for
table "my_table" does not exist


2017-05-23 20:15 GMT+06:00 Greg Sabino Mullane <greg at endpoint.com>:

> On Tue, May 23, 2017 at 04:17:28PM +0600, Пацев Антон wrote:
> > Hello Greg!
> > Thank for answer!
> > i write way for used bucardo.
> > Copy db using pg_dump from 9.3 to 9.5. Added db of bucardo. Added sync db
> > of bucardo.
> > Run sql query on 9.5 ALTER TABLE table ENABLE ALWAYS TRIGGER
> > trigger_create_table_partition_and_insert_function;
> > And get error:
> > ERROR:  trigger "trigger_create_table_partition_and_insert_function" for
> > table "table" does not exist
> >
> > May be wrong order?
>
> Well, I don't know how you setup your other database: I presume it has an
> identical schema. If not, you will need to add the trigger so that the
> inherited tables are populated. It should have the ame trigger and
> supporting
> function as the source database: the only change will be to set it to
> 'always'
> fire.
>
> --
> Greg Sabino Mullane greg at endpoint.com
> End Point Corporation
> PGP Key: 2529 DF6A B8F7 9407 E944  45B4 BC9B 9067 1496 4AC8
>



-- 
С уважением, Антон Пацев.
Best regards, Anton Patsev.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20170524/6d209bc8/attachment.html>