[Bucardo-general] Miscellaneous number of rows with bucardo 5.4.1 logical replication in the table where there are child tables
Пацев Антон
patsev.anton at gmail.com
Fri Jun 16 04:57:45 UTC 2017
Somebody, answer me?
2017-05-24 13:28 GMT+06:00 Пацев Антон <patsev.anton at gmail.com>:
> 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.
>
--
С уважением, Антон Пацев.
Best regards, Anton Patsev.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20170616/7866aa3e/attachment-0001.html>
More information about the Bucardo-general
mailing list