목적 : 온프레미스 PostgreSQL 데이터베이스 -> Aurora PostgreSQL-Compatible
select version();
PostgreSQL 12.15 (Ubuntu 12.15-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
show server_version;
12.15 (Ubuntu 12.15-1.pgdg20.04+1)
postgreSQL 설치 (AWS ec2에 테스트용 postgreSQL 설치)
grep . /etc/*-release
[root@ip-172-31-30-141 etc]# grep . /etc/*-release
/etc/amazon-linux-release:Amazon Linux release 2023.4.20240416 (Amazon Linux)
/etc/os-release:NAME="Amazon Linux"
/etc/os-release:VERSION="2023"
/etc/os-release:ID="amzn"
/etc/os-release:ID_LIKE="fedora"
/etc/os-release:VERSION_ID="2023"
/etc/os-release:PLATFORM_ID="platform:al2023"
/etc/os-release:PRETTY_NAME="Amazon Linux 2023.4.20240416"
/etc/os-release:ANSI_COLOR="0;33"
/etc/os-release:CPE_NAME="cpe:2.3:o:amazon:amazon_linux:2023"
/etc/os-release:HOME_URL="https://aws.amazon.com/linux/amazon-linux-2023/"
/etc/os-release:DOCUMENTATION_URL="https://docs.aws.amazon.com/linux/"
/etc/os-release:SUPPORT_URL="https://aws.amazon.com/premiumsupport/"
/etc/os-release:BUG_REPORT_URL="https://github.com/amazonlinux/amazon-linux-2023"
/etc/os-release:VENDOR_NAME="AWS"
/etc/os-release:VENDOR_URL="https://aws.amazon.com/"
/etc/os-release:SUPPORT_END="2028-03-15"
/etc/system-release:Amazon Linux release 2023.4.20240416 (Amazon Linux)
sudo dnf install postgresql15
[root@ip-172-31-30-141 etc]# sudo dnf install postgresql15
Last metadata expiration check: 18:03:33 ago on Mon May 20 16:30:28 2024.
Dependencies resolved.
==================================================================================================================================================================
Package Architecture Version Repository Size
==================================================================================================================================================================
Installing:
postgresql15 aarch64 15.6-1.amzn2023.0.1 amazonlinux 1.6 M
Installing dependencies:
postgresql15-private-libs aarch64 15.6-1.amzn2023.0.1 amazonlinux 139 k
Transaction Summary
==================================================================================================================================================================
Install 2 Packages
Total download size: 1.8 M
Installed size: 8.5 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): postgresql15-private-libs-15.6-1.amzn2023.0.1.aarch64.rpm 1.5 MB/s | 139 kB 00:00
(2/2): postgresql15-15.6-1.amzn2023.0.1.aarch64.rpm 12 MB/s | 1.6 MB 00:00
.. ...
Installed:
postgresql15-15.6-1.amzn2023.0.1.aarch64 postgresql15-private-libs-15.6-1.amzn2023.0.1.aarch64
Complete!
[root@ip-172-31-30-141 etc]#
sudo dnf install postgresql15-server
[root@ip-172-31-30-141 etc]# sudo dnf install postgresql15-server
Last metadata expiration check: 18:05:26 ago on Mon May 20 16:30:28 2024.
Dependencies resolved.
==================================================================================================================================================================
Package Architecture Version Repository Size
==================================================================================================================================================================
Installing:
postgresql15-server aarch64 15.6-1.amzn2023.0.1 amazonlinux 6.1 M
Installing dependencies:
libicu aarch64 67.1-7.amzn2023.0.3 amazonlinux 9.5 M
Transaction Summary
==================================================================================================================================================================
Install 2 Packages
Total download size: 16 M
Installed size: 65 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): libicu-67.1-7.amzn2023.0.3.aarch64.rpm 58 MB/s | 9.5 MB 00:00
(2/2): postgresql15-server-15.6-1.amzn2023.0.1.aarch64.rpm 30 MB/s | 6.1 MB 00:00
------------------------------------------------------------------------------------------------------------------------------------------------------------------
.. ...
Installed:
libicu-67.1-7.amzn2023.0.3.aarch64 postgresql15-server-15.6-1.amzn2023.0.1.aarch64
Complete!
[root@ip-172-31-30-141 etc]# exit
[ec2-user@ip-172-31-30-141 ~]$ sudo su
[root@ip-172-31-30-141 ec2-user]# su - postgres
Last login: Tue May 21 10:36:33 UTC 2024 on pts/1
[postgres@ip-172-31-30-141 ~]$ whoami
postgres
[postgres@ip-172-31-30-141 ~]$ psql -V
psql (PostgreSQL) 15.6
[postgres@ip-172-31-30-141 ~]$ pg_ctl initdb
[root@ip-172-31-30-141 ec2-user]# su - postgres
Last login: Tue May 21 10:36:33 UTC 2024 on pts/1
[postgres@ip-172-31-30-141 ~]$ whoami
postgres
[postgres@ip-172-31-30-141 ~]$ psql -V
psql (PostgreSQL) 15.6
[postgres@ip-172-31-30-141 ~]$
[postgres@ip-172-31-30-141 ~]$ pg_ctl initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
[postgres@ip-172-31-30-141 ~]$
서버 실행
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
[postgres@ip-172-31-30-141 ~]$ pg_ctl -D /var/lib/pgsql/data -l logfile start
waiting for server to start.... done
server started
스키마, 유저, 테이블 생성
[postgres@ip-172-31-30-141 ~]$ psql
psql (15.6)
Type "help" for help.
postgres=#
postgres=# create database sourcedb;
CREATE DATABASE
postgres=# create user test password 'test' superuser;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | Superuser | {}
postgres=#
postgres=# create database testdb owner test;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb | test | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
(4 rows)
postgres=#
[postgres@ip-172-31-30-141 ~]$ psql -U test -W -d testdb
Password:
psql (15.6)
Type "help" for help.
testdb=#
우분투 postgreSQL 설치.
ubuntu@ip-172-31-18-33:~$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
ubuntu@ip-172-31-18-33:~$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).
OK
ubuntu@ip-172-31-18-33:~$ sudo apt-get update
Hit:1 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble InRelease
Hit:2 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble-updates InRelease
Hit:3 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble-backports InRelease
Hit:4 http://security.ubuntu.com/ubuntu noble-security InRelease
Get:5 http://apt.postgresql.org/pub/repos/apt noble-pgdg InRelease [123 kB]
Get:6 http://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 Packages [278 kB]
Fetched 401 kB in 3s (121 kB/s)
Reading package lists... Done
W: http://apt.postgresql.org/pub/repos/apt/dists/noble-pgdg/InRelease: Key is stored in legacy trusted.gpg keyring (/etc/apt/trusted.gpg), see the DEPRECATION section in apt-key(8) for details.
ubuntu@ip-172-31-18-33:~$ sudo apt-get -y install postgresql-12
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libllvm17t64 libpq5 libtypes-serialiser-perl postgresql-client-12 postgresql-client-common postgresql-common ssl-cert
Suggested packages:
postgresql-doc-12
The following NEW packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libllvm17t64 libpq5 libtypes-serialiser-perl postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common
ssl-cert
0 upgraded, 11 newly installed, 0 to remove and 32 not upgraded.
Need to get 42.4 MB of archives.
After this operation, 180 MB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 postgresql-client-common all 260.pgdg24.04+1 [36.0 kB]
Get:2 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble/main amd64 libjson-perl all 4.10000-1 [81.9 kB]
Get:3 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble/main amd64 ssl-cert all 1.1.2ubuntu1 [17.8 kB]
Get:4 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble/main amd64 libcommon-sense-perl amd64 3.75-3build3 [20.4 kB]
Get:5 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble/main amd64 libtypes-serialiser-perl all 1.01-1 [11.6 kB]
Get:6 http://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 postgresql-common all 260.pgdg24.04+1 [169 kB]
Get:7 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble/main amd64 libjson-xs-perl amd64 4.030-2build3 [83.6 kB]
Get:8 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble/main amd64 libllvm17t64 amd64 1:17.0.6-9ubuntu1 [26.2 MB]
Get:9 http://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 libpq5 amd64 16.3-1.pgdg24.04+1 [218 kB]
Get:10 http://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 postgresql-client-12 amd64 12.19-1.pgdg24.04+1 [1439 kB]
Get:11 http://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 postgresql-12 amd64 12.19-1.pgdg24.04+1 [14.2 MB]
Fetched 42.4 MB in 5s (7732 kB/s)
... ...
ubuntu@ip-172-31-18-33:~$ psql --version
psql (PostgreSQL) 12.19 (Ubuntu 12.19-1.pgdg24.04+1)
ubuntu@ip-172-31-18-33:~$
sudo service postgresql stop
sudo service postgresql start
sudo service postgresql status
ubuntu@ip-172-31-18-33:~$ sudo service postgresql stop
ubuntu@ip-172-31-18-33:~$ sudo service postgresql start
ubuntu@ip-172-31-18-33:~$ sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
Active: active (exited) since Fri 2024-05-24 07:47:38 UTC; 2s ago
Process: 7922 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 7922 (code=exited, status=0/SUCCESS)
CPU: 3ms
May 24 07:47:38 ip-172-31-18-33 systemd[1]: Starting postgresql.service - PostgreSQL RDBMS...
May 24 07:47:38 ip-172-31-18-33 systemd[1]: Finished postgresql.service - PostgreSQL RDBMS.
ubuntu@ip-172-31-18-33:~$
ubuntu@ip-172-31-18-33:~$ psql -V
psql (PostgreSQL) 12.19 (Ubuntu 12.19-1.pgdg24.04+1)
sourceDB, targetDB, bucardo 만들기
postgres@ip-172-31-18-33:~$ psql
psql (12.19 (Ubuntu 12.19-1.pgdg24.04+1))
Type "help" for help.
create user sdbusr password 'sdbpwd' superuser;
create database sdb owner sdbusr;
create user ddbusr password 'ddbpwd' superuser;
create database ddb owner ddbusr;
\du
\l
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
ddbusr | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
sdbusr | Superuser | {}
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
ddb | ddbusr | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
sdb | sdbusr | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
postgres=#
psql -U sdbusr -W -d sdb -h 127.0.0.1
psql -U ddbusr -W -d ddb -h 127.0.0.1
postgres@ip-172-31-18-33:~$ psql -U sdbusr -W -d sdb -h 127.0.0.1
Password:
psql (12.19 (Ubuntu 12.19-1.pgdg24.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
sdb=# exit
sdb에 테이블 생성 및 데이터 넣기.
sdb=# create table release_planning(
ID INTEGER NOT NULL,
NAME TEXT,
PLANNING_START_DATE DATE,
PLANNING_END_DATE DATE,
DESCRIPTION TEXT,
LAST_UPDATE DATE,
CREATED DATE
)
;
CREATE TABLE
sdb=# insert into release_planning
select n,
substr(concat(md5(random()::text), md5(random()::text)), 0, 35),
current_date,
current_date + (round(random()*100)::integer),
substr(concat(md5(random()::text), md5(random()::text)), 0, 20),
current_date + (round(random()*100)::integer),
current_date + (round(random()*100)::integer)
from generate_series (1, 100000) n;
INSERT 0 100000
sdb=# select * from release_planning limit 1;
id | name | planning_start_date | planning_end_date | description | last_update | created
----+------------------------------------+---------------------+-------------------+---------------------+-------------+------------
1 | 2a0aaf124ac0603bfc903c952b2a9c895f | 2024-05-24 | 2024-07-06 | 9ab2b2968f9d1231b9e | 2024-06-15 | 2024-06-16
(1 row)
sudo apt-get install libdbix-safe-perl libdbd-pg-perl libboolean-perl postgresql-plperl-12
ubuntu@ip-172-31-18-33:~$ sudo apt-get install libdbix-safe-perl libdbd-pg-perl libboolean-perl postgresql-plperl-12
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
libdbi-perl
Suggested packages:
libclone-perl libmldbm-perl libnet-daemon-perl libsql-statement-perl
The following NEW packages will be installed:
libboolean-perl libdbd-pg-perl libdbi-perl libdbix-safe-perl postgresql-plperl-12
0 upgraded, 5 newly installed, 0 to remove and 1 not upgraded.
Need to get 1013 kB of archives.
After this operation, 3126 kB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble/universe amd64 libboolean-perl all 0.46-3 [8430 B]
Get:2 http://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 libdbd-pg-perl amd64 3.18.0-1.pgdg24.04+2 [186 kB]
Get:3 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble/main amd64 libdbi-perl amd64 1.643-4build3 [721 kB]
Get:4 http://ap-northeast-2.ec2.archive.ubuntu.com/ubuntu noble/universe amd64 libdbix-safe-perl all 1.2.5-4 [13.4 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 postgresql-plperl-12 amd64 12.19-1.pgdg24.04+1 [84.1 kB]
Fetched 1013 kB in 2s (521 kB/s)
Selecting previously unselected package libboolean-perl.
(Reading database ... 73941 files and directories currently installed.)
Preparing to unpack .../libboolean-perl_0.46-3_all.deb ...
Unpacking libboolean-perl (0.46-3) ...
Selecting previously unselected package libdbi-perl:amd64.
Preparing to unpack .../libdbi-perl_1.643-4build3_amd64.deb ...
Unpacking libdbi-perl:amd64 (1.643-4build3) ...
Selecting previously unselected package libdbd-pg-perl.
Preparing to unpack .../libdbd-pg-perl_3.18.0-1.pgdg24.04+2_amd64.deb ...
Unpacking libdbd-pg-perl (3.18.0-1.pgdg24.04+2) ...
Selecting previously unselected package libdbix-safe-perl.
Preparing to unpack .../libdbix-safe-perl_1.2.5-4_all.deb ...
Unpacking libdbix-safe-perl (1.2.5-4) ...
Selecting previously unselected package postgresql-plperl-12.
Preparing to unpack .../postgresql-plperl-12_12.19-1.pgdg24.04+1_amd64.deb ...
Unpacking postgresql-plperl-12 (12.19-1.pgdg24.04+1) ...
Setting up libboolean-perl (0.46-3) ...
Setting up postgresql-plperl-12 (12.19-1.pgdg24.04+1) ...
Setting up libdbi-perl:amd64 (1.643-4build3) ...
Setting up libdbd-pg-perl (3.18.0-1.pgdg24.04+2) ...
Setting up libdbix-safe-perl (1.2.5-4) ...
Processing triggers for postgresql-common (260.pgdg24.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Processing triggers for man-db (2.12.0-4build2) ...
Scanning processes...
Scanning candidates...
Scanning linux images...
Running kernel seems to be up-to-date.
Restarting services...
Service restarts being deferred:
/etc/needrestart/restart.d/dbus.service
systemctl restart getty@tty1.service
systemctl restart networkd-dispatcher.service
systemctl restart serial-getty@ttyS0.service
systemctl restart systemd-logind.service
systemctl restart unattended-upgrades.service
No containers need to be restarted.
User sessions running outdated binaries:
ubuntu @ session #2: sshd[1045,1156]
ubuntu @ user manager service: systemd[1051]
No VM guests are running outdated hypervisor (qemu) binaries on this host.
ubuntu@ip-172-31-18-33:~$
sudo apt-get install postgresql-contrib-12 postgresql-server-dev-12
ubuntu@ip-172-31-18-33:~$ sudo apt-get install postgresql-contrib-12 postgresql-server-dev-12
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Note, selecting 'postgresql-12' instead of 'postgresql-contrib-12'
postgresql-12 is already the newest version (12.19-1.pgdg24.04+1).
The following additional packages will be installed:
binutils binutils-common binutils-x86-64-linux-gnu clang-17 gcc-13-base icu-devtools libasan8 libatomic1 libbinutils libclang-common-17-dev libclang-cpp17t64
libclang-rt-17-dev libclang1-17t64 libctf-nobfd0 libctf0 libffi-dev libgc1 libgcc-13-dev libgomp1 libgprofng0 libhwasan0 libicu-dev libitm1 liblsan0 libncurses-dev
libobjc-13-dev libobjc4 libpfm4 libpq-dev libquadmath0 libsframe1 libssl-dev libstdc++-13-dev libtsan2 libubsan1 libxml2-dev libz3-4 libz3-dev llvm-17 llvm-17-dev
llvm-17-linker-tools llvm-17-runtime llvm-17-tools
Suggested packages:
binutils-doc gprofng-gui clang-17-doc wasi-libc icu-doc ncurses-doc postgresql-doc-16 libssl-doc libstdc++-13-doc pkg-config llvm-17-doc
The following NEW packages will be installed:
binutils binutils-common binutils-x86-64-linux-gnu clang-17 gcc-13-base icu-devtools libasan8 libatomic1 libbinutils libclang-common-17-dev libclang-cpp17t64
libclang-rt-17-dev libclang1-17t64 libctf-nobfd0 libctf0 libffi-dev libgc1 libgcc-13-dev libgomp1 libgprofng0 libhwasan0 libicu-dev libitm1 liblsan0 libncurses-dev
libobjc-13-dev libobjc4 libpfm4 libpq-dev libquadmath0 libsframe1 libssl-dev libstdc++-13-dev libtsan2 libubsan1 libxml2-dev libz3-4 libz3-dev llvm-17 llvm-17-dev
llvm-17-linker-tools llvm-17-runtime llvm-17-tools postgresql-server-dev-12
0 upgraded, 44 newly installed, 0 to remove and 1 not upgraded.
Need to get 136 MB of archives.
After this operation, 785 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
... ...
dpkg --list | grep postgresql
ubuntu@ip-172-31-18-33:~$ dpkg --list | grep postgresql
ii postgresql-12 12.19-1.pgdg24.04+1 amd64 The World's Most Advanced Open Source Relational Database
ii postgresql-client-12 12.19-1.pgdg24.04+1 amd64 front-end programs for PostgreSQL 12
ii postgresql-client-common 260.pgdg24.04+1 all manager for multiple PostgreSQL client versions
ii postgresql-common 260.pgdg24.04+1 all PostgreSQL database-cluster manager
ii postgresql-plperl-12 12.19-1.pgdg24.04+1 amd64 PL/Perl procedural language for PostgreSQL 12
ii postgresql-server-dev-12 12.19-1.pgdg24.04+1 amd64 development files for PostgreSQL 12 server-side programming
ubuntu@ip-172-31-18-33:~$
참조해서 진행.
https://medium.com/@logeshmohan/postgresql-replication-using-bucardo-5-4-1-6e78541ceb5e
mkdir -p /var/log/bucardo /var/run/bucardo
chown -R postgres:postgres /var/log/bucardo /var/run/bucardo
root@ip-172-31-18-33:/tmp# wget --user-agent="Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.103 Safari/537.36" https://bucardo.org/downloads/Bucardo-5.6.0.tar.gz
--2024-05-24 08:15:33-- https://bucardo.org/downloads/Bucardo-5.6.0.tar.gz
Resolving bucardo.org (bucardo.org)... 104.21.20.149, 172.67.193.37, 2606:4700:3034::ac43:c125, ...
Connecting to bucardo.org (bucardo.org)|104.21.20.149|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 335100 (327K) [application/x-gzip]
Saving to: ‘Bucardo-5.6.0.tar.gz’
Bucardo-5.6.0.tar.gz 100%[=============================================================================================>] 327.25K --.-KB/s in 0.01s
2024-05-24 08:15:34 (30.1 MB/s) - ‘Bucardo-5.6.0.tar.gz’ saved [335100/335100]
root@ip-172-31-18-33:/tmp# tar xvzf Bucardo-5.6.0.tar.gz
... ...
root@ip-172-31-18-33:/tmp# cd Bucardo-5.6.0
root@ip-172-31-18-33:/tmp/Bucardo-5.6.0# perl Makefile.PL
Checking if your kit is complete...
Looks good
Warning: prerequisite CGI 0 not found.
Warning: prerequisite Encode::Locale 0 not found.
Warning: prerequisite Pod::Parser 0 not found.
Generating a Unix-style Makefile
Writing Makefile for Bucardo
Writing MYMETA.yml and MYMETA.json
root@ip-172-31-18-33:/tmp/Bucardo-5.6.0# apt install make
...
The following NEW packages will be installed:
make
0 upgraded, 1 newly installed, 0 to remove and 1 not upgraded.
...
root@ip-172-31-18-33:/tmp/Bucardo-5.6.0# make
cp bucardo.schema blib/share/bucardo.schema
cp Bucardo.pm blib/lib/Bucardo.pm
cp bucardo blib/script/bucardo
"/usr/bin/perl" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/bucardo
Manifying 1 pod document
Manifying 1 pod document
root@ip-172-31-18-33:/tmp/Bucardo-5.6.0# make install
Manifying 1 pod document
Manifying 1 pod document
Installing /usr/local/share/perl/5.38.2/Bucardo.pm
Installing /usr/local/man/man1/bucardo.1pm
Installing /usr/local/man/man3/Bucardo.3pm
Installing /usr/local/bin/bucardo
Installing /usr/local/share/bucardo/bucardo.schema
Appending installation info to /usr/local/lib/x86_64-linux-gnu/perl/5.38.2/perllocal.pod
root@ip-172-31-18-33:/tmp/Bucardo-5.6.0#
cat /etc/passwd
postgres:x:111:114:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
ubuntu@ip-172-31-18-33:~$ sudo su - postgres
postgres@ip-172-31-18-33:~$
postgres@ip-172-31-18-33:~$ cat .bucardorc
dbhost=127.0.0.1
dbname=bucardo
dbport=5432
dbuser=bucardo
postgres@ip-172-31-18-33:~$
triggers
export SOURCE_HOST=<VALUE_OF_SOURCE_DB_HOST>
export SOURCE_PORT=<SOURCE_DB_PORT>
export SOURCE_DATABASE=<SOURCE_DB_NAME>
export SOURCE_USERNAME=<SOURCE_DB_USERNAME>
export SOURCE_PASSWORD=<SOURCE_DB_PASSWORD>
export SOURCE_HOST=127.0.0.1
export SOURCE_PORT=5432
export SOURCE_DATABASE=sdb
export SOURCE_USERNAME=sdbusr
export SOURCE_PASSWORD=sdbpwd
echo "127.0.0.1:5432:bucardo:bucardo:<SET_PASSWORD_FOR_LOCAL_DB>" > $HOME/.pgpass
chmod 0600 $HOME/.pgpass
echo "127.0.0.1:5432:bucardo:bucardo:bucardopwd" > $HOME/.pgpass
chmod 0600 $HOME/.pgpass
postgres@ip-172-31-18-33:~$ cat >> $HOME/.pgpass <<EOL
$DEST_HOST:$DEST_PORT:$DEST_DATABASE:$DEST_USERNAME:$DEST_PASSWORD
$SOURCE_HOST:$SOURCE_PORT:$SOURCE_DATABASE:$SOURCE_USERNAME:$SOURCE_PASSWORD
EOL
postgres@ip-172-31-18-33:~$ cat .pgpass
127.0.0.1:5432:bucardo:bucardo:bucardopwd
127.0.0.1:5432:ddb:ddbusr:ddbpwd
127.0.0.1:5432:sdb:sdbusr:sdbpwd
bucardo 유저, DB 생성.
sudo su - postgres -c "psql postgres"
sudo su - postgres -c "psql postgres"
CREATE USER bucardo WITH LOGIN SUPERUSER ENCRYPTED PASSWORD 'bucardopwd';
CREATE DATABASE bucardo;
CREATE EXTENSION plperl;
postgres=# CREATE USER bucardo WITH LOGIN SUPERUSER ENCRYPTED PASSWORD 'bucardopwd';
CREATE ROLE
postgres=# CREATE DATABASE bucardo;
CREATE DATABASE
postgres=# CREATE EXTENSION plperl;
CREATE EXTENSION
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
bucardo | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
ddb | ddbusr | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
sdb | sdbusr | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(6 rows)
postgres=#
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
bucardo | Superuser | {}
ddbusr | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
sdbusr | Superuser | {}
postgres=#
postgres@ip-172-31-18-33:~$ psql -U bucardo -W -d bucardo -h 127.0.0.1
Password:
psql (12.19 (Ubuntu 12.19-1.pgdg24.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
bucardo=# GRANT ALL ON DATABASE bucardo TO bucardo;
GRANT
bucardo=# CREATE TABLE bucardo_config (
name TEXT NOT NULL, -- short unique name, maps to %config inside Bucardo
setting TEXT NOT NULL,
about TEXT NULL, -- long description
type TEXT NULL, -- sync or goat
item TEXT NULL, -- which specific sync or goat
cdate TIMESTAMPTZ NOT NULL DEFAULT now()
);
COMMENT ON TABLE bucardo_config IS $$Contains configuration variables for a specific Bucardo instance$$;
CREATE TABLE
COMMENT
bucardo=# CREATE UNIQUE INDEX bucardo_config_unique ON bucardo_config(LOWER(name)) WHERE item IS NULL;
CREATE UNIQUE INDEX bucardo_config_unique_name ON bucardo_config(name,item,type) WHERE item IS NOT NULL;
ALTER TABLE bucardo_config ADD CONSTRAINT valid_config_type CHECK (type IN ('sync','goat'));
ALTER TABLE bucardo_config ADD CONSTRAINT valid_config_isolation_level
CHECK (name <> 'isolation_level' OR (setting IN ('serializable','repeatable read')));
CREATE INDEX
CREATE INDEX
ALTER TABLE
ALTER TABLE
bucardo install — quiet
postgres@ip-172-31-18-33:~$ bucardo install — quiet
This will install the bucardo database into an existing Postgres cluster.
Postgres must have been compiled with Perl support,
and you must connect as a superuser
Current connection settings:
1. Host: 127.0.0.1
2. Port: 5432
3. User: bucardo
4. Database: bucardo
5. PID directory: /var/run/bucardo
Enter a number to change it, P to proceed, or Q to quit: q
Goodbye!
postgres@ip-172-31-18-33:~$
pg_dump
postgres@ip-172-31-18-33:~$ export SOURCE_HOST=127.0.0.1
export SOURCE_PORT=5432
export SOURCE_DATABASE=sdb
export SOURCE_USERNAME=sdbusr
export SOURCE_PASSWORD=sdbpwd
export DEST_HOST=127.0.0.1
export DEST_PORT=5432
export DEST_DATABASE=ddb
export DEST_USERNAME=ddbusr
export DEST_PASSWORD=ddbpwd
postgres@ip-172-31-18-33:~$ pg_dump "host=$SOURCE_HOST port=$SOURCE_PORT dbname=$SOURCE_DATABASE user=$SOURCE_USERNAME" --schema-only | grep -v 'CREATE TRIGGER' | grep -v '^--' | grep -v '^$' | grep -v '^SET' | grep -v 'OWNER TO' > schema.sql
postgres@ip-172-31-18-33:~$ cat schema.sql
SELECT pg_catalog.set_config('search_path', '', false);
CREATE TABLE public.release_planning (
id integer NOT NULL,
name text,
planning_start_date date,
planning_end_date date,
description text,
last_update date,
created date
);
postgres@ip-172-31-18-33:~$
bucardo add db
bucardo add db source_db dbhost=$SOURCE_HOST dbport=$SOURCE_PORT dbname=$SOURCE_DATABASE dbuser=$SOURCE_USERNAME dbpass=$SOURCE_PASSWORD
부카르도 관련. 권한.
https://installvirtual.com/how-to-install-bucardo-for-postgres-replication/
DMS
SCT : AWS DMS는 데이터 마이그레이션과 특별히 관련이 없는 보조 인덱스, 시퀀스, 기본값, 저장 프로시저, 트리거, 동의어, 뷰 및 기타 스키마 객체를 마이그레이션하지 않습니다. 이러한 객체를 PostgreSQL 대상으로 마이그레이션하려면 AWS SCT를 사용
AWS Schema Conversion Tool(AWS SCT)
https://docs.aws.amazon.com/ko_kr/SchemaConversionTool/latest/userguide/CHAP_GettingStarted.html
CDC
참고 : https://omty.tistory.com/53
LOB데이터
참고 문서
AWS 규범적 지침 - 온프레미스 PostgreSQL 데이터베이스를 Amazon EC2로 마이그레이션하기
bucardo
온프레미스 PostgreSQL 데이터베이스를 Amazon EC2로 마이그레이션하기
- 소스 데이터베이스가 이전 버전의 PostgreSQL (PostgreSQL 9.2 이전 버전) 에서 실행되고 있습니다.
- 한 클라우드 공급자에서 다른 온라인 공급자로 PostgreSQL 데이터베이스를 마이그레이션하고 있습니다.
https://bucardo.org/Bucardo/installation/
pg_dump, pg_restore 를 사용하여 백업 및 복원
- 대규모 데이터베이스 (즉, 300GB를 초과하는 데이터베이스) 의 경우 덤프 및 복원에 많은 시간이 소 요될 수 있습니다.
- 이 옵션을 사용하려면 완전한 데이터베이스 다운타임이 필요합니다.
1. pg_basebackup PostgreSQL 유틸리티를 사용하여 온프레미스 데이터베이스에서 데이터를 물리적 으로 백업한 다음 해당 백업을 AWS 클라우드의 Amazon EC2 데이터 디렉터리 위치에 복사합니다.
2. PostgreSQL 마이그레이션을 완료합니다.
3. 데이터베이스에서 VACUUM 작업을 수행합니다.
4. 데이터 검증 5. 애플리케이션에서 EC2 인스턴스의 새 PostgreSQL 데이터베이스를 가리키도록 합니다
DMS 사용 가능 버전 확인
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html
RDS 생성
DB instance identifier : database-dms-test
Master username : postgres
Self managed : testtest
-> Create
DMS Started (복제 인스턴스는 중지 기능이 없음)
Migrate -> Instance-based migrations -> Create replication instance ->
bucardo 설치
https://bucardo.org/Bucardo/installation/
https://smartcar.com/blog/zero-downtime-migration
12.
패키지 조회.
dpkg --list
=====
RDS
Configuration
============
============
Deletion protection
- Enabled
Architecture settings
- Non-multitenant architecture
Instance class
============
Multi-AZ
Yes
Storage
=============
Storage type
Provisioned IOPS SSD (io1)
Storage
1024 GiB
Provisioned IOPS
1000 IOPS
Encryption
Enabled
Storage autoscaling
Disabled
버전 비교
SELECT aurora_version();