Hello Greg! My way sync using bucardo:
*On PostgreSQL 9.3:* [[email protected] 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 [[email protected] 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 [email protected]:~ [email protected]'s password: my_database.sql.tar 100% 19MB 18.8MB/s 00:00 -bash-4.1$ logout [[email protected] bucardo-updated]# ./replica_sync_add.sh -------------------------------------------------- DBNAME - my_database -------------------------------------------------- Added sync "my_database_sync" -------------------------------------------------- done [[email protected] bucardo-updated]# bucardo start Checking for existing processes Starting Bucardo [[email protected] 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 | [[email protected] bucardo-updated]# cd /; sudo -u postgres psql psql (9.3.5) Type "help" for help. [local]:5432 postgres@postgres # \c my_database You are now connected to database "my_database" as user "postgres". [local]:5432 postgres@my_database # SELECT count(*) from my_table; count -------- 677484 (1 row) [local]:5432 postgres@my_database # SELECT count(*) from my_table; count -------- 677545 (1 row) [local]:5432 postgres@my_database # \q You have new mail in /var/spool/mail/root [[email protected] /]# 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 | [[email protected] /]# cd /; sudo -u postgres psql psql (9.3.5) [local]:5432 postgres@postgres # \c my_database You are now connected to database "my_database" as user "postgres". [local]:5432 postgres@my_database # SELECT count(*) from my_table; count -------- 677545 (1 row) [local]:5432 postgres@my_database # *On PostgreSQL 9.5:* ➤ psql://[email protected]:5432/postgres # DROP DATABASE my_database; DROP DATABASE Time: 196.678 ms ➤ psql://[email protected]:5432/postgres # create database my_database with owner my_database; CREATE DATABASE Time: 864.592 ms ➤ psql://[email protected]:5432/postgres # \q [[email protected] /]# mc [[email protected] 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 [[email protected] 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://[email protected]:5432/my_database # SELECT count(*) from my_table; count -------- 951939 (1 row) ➤ psql://[email protected]: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 <[email protected]>: > 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 [email protected] > End Point Corporation > PGP Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 > -- С уважением, Антон Пацев. Best regards, Anton Patsev.
_______________________________________________ Bucardo-general mailing list [email protected] https://mail.endcrypt.com/mailman/listinfo/bucardo-general
