Setup relicate table Postgres

 

SHOW wal_level;

ALTER SYSTEM SET wal_level = logical;

On Publisher database:

--STEP 1: create publiccation named mypub, for table1,table2, table3
CREATE PUBLICATION mypub FOR TABLE newtable;

--check created publication
SELECT *
FROM pg_catalog.pg_publication

SELECT *
FROM pg_catalog.pg_publication_tables

--STEP 2: for each subscription, need to creating relication slot on publication node, 'pgoutput' is plugin
SELECT pg_create_logical_replication_slot('mysub', 'pgoutput');

--check replication slot
SELECT slot_name, slot_type, active FROM pg_replication_slots;

--in case to manual delete replication slot, run
SELECT * FROM pg_drop_replication_slot('slot_name');

 

On Subscriber database

 

--create subscription

CREATE SUBSCRIPTION mysub
CONNECTION 'host=192.168.56.20 port=5432 user=databaseuser password=passhere dbname=dbtest1'
PUBLICATION mypub
WITH (create_slot=false);

--check all subscription
SELECT *
FROM pg_subscription

-- drop subscription
ALTER SUBSCRIPTION mysub DISABLE;
ALTER SUBSCRIPTION mysub SET (slot_name=NONE);
DROP SUBSCRIPTION mysub

Fix Sequence Postgres

 SELECT
    'SELECT SETVAL(' ||
       quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| '), 1) ) FROM ' ||
       quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| ';'
FROM pg_depend
    INNER JOIN pg_class AS class_sequence
        ON class_sequence.oid = pg_depend.objid
            AND class_sequence.relkind = 'S'
    INNER JOIN pg_class AS class_table
        ON class_table.oid = pg_depend.refobjid
    INNER JOIN pg_attribute
        ON pg_attribute.attrelid = class_table.oid
            AND pg_depend.refobjsubid = pg_attribute.attnum
    INNER JOIN pg_namespace as table_namespace
        ON table_namespace.oid = class_table.relnamespace
    INNER JOIN pg_namespace AS sequence_namespace
        ON sequence_namespace.oid = class_sequence.relnamespace
ORDER BY sequence_namespace.nspname, class_sequence.relname;

 

 

Ref: https://wiki.postgresql.org/wiki/Fixing_Sequences

 

Trí Tuệ Kinh Dịch: Bát Quái Chính Là 8 Loại Tu Dưỡng Lớn Nhất Đời Người

 

Kinh Dịch nói rằng: 5 kiểu người này luôn gặp may mắn và phúc báu

 

Fix lc_* message error when start postgres

 localedef -i en_US -c -f UTF-8 -A /usr/share/locale/locale.alias en_US.UTF-8

Install nftables

apt update 

apt install make git autoconf autogen libtool bison flex asciidoc libgmp-dev libreadline-dev pkg-config 

echo "install libmnl 1.0.4"
git clone -b libmnl-1.0.4 git://git.netfilter.org/libmnl
cd libmnl
sh autogen.sh
./configure
make -j 4 install

echo "install libnftnl-1.2.0"
cd
git clone -b libnftnl-1.2.0 git://git.netfilter.org/libnftnl
cd libnftnl
sh autogen.sh
./configure --libdir=/lib/x86_64-linux-gnu/
make -j 4 install

 
echo "install nftables v0.9.9"
cd
git clone -b v0.9.9 git://git.netfilter.org/nftables
cd nftables
sh autogen.sh
./configure --libdir=/lib/x86_64-linux-gnu/
make -j 4 install

echo "add crontab on reboot"
echo "@reboot root    nft -f /etc/nftables.conf" >> /etc/crontab

 

Build mysql from Source

 apt build-dep mariadb-server


mkdir mariadb-build
cd mariadb-build
git clone -b 10.5 https://github.com/MariaDB/server.git
mkdir mariadb
cd mariadb
cmake ../server -DBUILD_CONFIG=mysql_release
make package

 

Wait about 30 min for build, it will export binary file tarball .tar.gz

Copy that file to home and extract it

cd mariadb-version

Create some directories for log, config...

mkdir log
mkdir tmp
mkdir database
mkdir binlog

Create my.cnf file and .sh files for simplified action

Copy shared libraries to portablelib, use generate_portablelib.sh