Category Archives: AWS

Upgrade RDS PostgreSQL to higher version with less downtime using bucardo

Downtime ! when the business team listen to this word they simply say no…To upgrade a PostgreSQL Database using pg_upgrade we need good amount of downtime based on the database size. Even if we are using PostgreSQL RDS we need to have downtime to upgrade to latest versions.

If we are using PostgreSQL 9.4+ we can use the pglogical extension, some times it might not be possible to install this extension and we need another way. In that I found bucardo is very much useful. We can install this on a separate machine and configure the replication.

Let’s start!

What is bucardo ?

Bucardo is a perl based software used to replicate a set of tables from source to destination using triggers. I am using this to migrate from RDS PostgreSQL 9.5 to 11 version.

How bucardo works ?

Bucardo creates a schema bucardo in the source database and create a trigger on each table who are part of replication. Once there is a change (insert, update, delete) on source table, it will be stored in the delta tables created in bucardo schema. A notice will be sent to the bucardo daemon process which will handle the replication of record to destination.

My configuration ?

I have two rds instances and one EC2 instance with CentOS 7.



The simplest way to install bucardo is using the yum repository of PostgreSQL. After installation the configure bucardo to connect to all databases (source and destination) without prompting password. Create the users with rds_superuser permissions and make entry for them in the pg_hba.conf of bucardo instance. As this is my private instance I created users with same password and used * for all instances in pg_hba.conf

yum install epel
yum install bucardo_10
[root@bucardo usr]# systemctl enable postgresql-10
Created symlink from /etc/systemd/system/ to /usr/lib/systemd/system/postgresql-10.service.
[root@bucardo usr]# /usr/pgsql-10/bin/postgresql-10-setup initdb
Initializing database ... OK
[root@bucardo usr]# systemctl start postgresql-10
# Set the configuration parameters
postgres=# alter system set listen_addresses to '*';
postgres=# alter system set shared_buffers to '250MB';
# Add a line to pg_hba.conf
host    all             all                  md5
# Create a file ~/.pgpass file with below content add in all hosts
-bash-4.2$ cat ~/.pgpass
# I have set password for all users to postgres so I set it like that
systemctl restart postgresql-10

Create bucardo repository database

bucardo use repository database to maintain the metadata about the instances involved in the replication and other information. Login to the bucardo instance and create the database.

postgres=# create user bucardo password 'postgres' superuser;
postgres=# create database bucardo owner bucardo;

Install bucardo repository

This will create metadata tables required to store metadata in the bucardo database created in previous step.

To store the log files and process id related files create the required folders.

[root@bucardo usr]# mkdir -p /var/run/bucardo
[root@bucardo usr]# mkdir -p /var/log/bucardo
[root@bucardo usr]# chown -R postgres:postgres /var/run/bucardo
[root@bucardo usr]# chown -R postgres:postgres /var/log/bucardo

install bucardo

-bash-4.2$ bucardo install
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:           <none>
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: 1

# Change hostname to localhost and select P to proceed
Enter a number to change it, P to proceed, or Q to quit: P

Attempting to create and populate the bucardo database and schema
Database creation is complete

Updated configuration setting "piddir"
Installation is now complete.
If you see errors or need help, please email

You may want to check over the configuration variables next, by running:
bucardo show all
Change any setting by using: bucardo set foo=bar

Now the bucardo installation is completed. We will configure the instances for replication.

Configure instances

Take a schema backup of the compliance database from 9.5 and restore it to 11 version server.

pg_dump -C -d compliance -h --schema-only | psql
# Install the extension plperl in both the instances
# For 9.5,11 version install plperl
yum install postgresql11-plperl
compliance=# create extension plperl;

As we are using AWS create the below function in source and destination environments (databases)

postgres=# CREATE OR REPLACE FUNCTION public.rds_session_replication_role(role text)
postgres-#  RETURNS text
postgres-#  LANGUAGE plpgsql
postgres-# AS $function$
postgres$#         DECLARE
postgres$#                 curr_val text := 'unset';
postgres$#         BEGIN
postgres$#                 EXECUTE 'SET session_replication_role = ' || quote_literal(role);
postgres$#                 EXECUTE 'SHOW session_replication_role' INTO curr_val;
postgres$#                 RETURN curr_val;
postgres$#         END
postgres$# $function$;
postgres=# revoke all on function rds_session_replication_role(text) from public;
Also, make changes to the /usr/local/share/perl5/ file at lines 5397 and 5330 with the following code:
$dbh->do(q{select rds_session_replication_role('replica');}); ## Assumes a sane default !
$dbh->do(q{SET session_replication_role = default}); ## Assumes a sane default !

Add databases to bucardo

Add the databases into bucardo , to be part of replication and create a database group.

postgres@bucardo $ bucardo add db pgdb95 dbhost= dbport=5432 dbname=compliance dbuser=bucardo dbpass=postgres
Added database "pgdb95"
postgres@bucardo $ bucardo add db pgdb11 dbhost= dbport=5432 dbname=compliance dbuser=bucardo dbpass=postgres
Added database "pgdb11"
postgres@bucardo $
postgres@bucardo $ bucardo list db
Database: pgdb11  Status: active  Conn: psql -p 5432 -U bucardo -d compliance -h
Database: pgdb95  Status: active  Conn: psql -p 5432 -U bucardo -d compliance -h
postgres@bucardo $
postgres@bucardo $ bucardo add dbgroup migration_group
Created dbgroup "migration_group"
postgres@bucardo $
[root@bucardo ~]# bucardo add dbgroup migration_group pgdb95:source
Added database "pgdb95" to dbgroup "migration_group" as source
[root@bucardo ~]# bucardo add dbgroup migration_group pgdb11:target
Added database "pgdb11" to dbgroup "migration_group" as target
postgres@bucardo $

Add tables and sequences to be part of replication

We can add some specific tables to be part of replication process. Here I am adding all tables because of the requirement. This will not start replication it just note them as herds(relation groups).

[root@bucardo ~]# bucardo add all tables db=pgdb95 relgroup=migration_rel_group
Creating relgroup: migration_rel_group
Added table public.pgbench_branches to relgroup migration_rel_group
Added table public.pgbench_tellers to relgroup migration_rel_group
Added table public.pgbench_history to relgroup migration_rel_group
Added table public.pgbench_accounts to relgroup migration_rel_group
New tables added: 0
Already added: 4
[root@bucardo ~]#
[root@bucardo ~]# bucardo add all sequences db=pgdb95 relgroup=migration_rel_group --verbose
Relgroup already exists: migration_rel_group
Added sequence public.pgbench_history_sid_seq to relgroup migration_rel_group
New sequences added: 0
Already added: 1

[root@bucardo ~]# bucardo list herd
Relgroup: migration_rel_group  DB: pgdb95  Members: public.pgbench_accounts, public.pgbench_branches, public.pgbench_history, public.pgbench_history_sid_seq, public.pgbench_tellers

Create sync

Sync is the one which will start the replication by creating the triggers and other processes. Here I am creating the sync with “autokick=0” which means it will make a note of the changes (insert, update, delete) but not replicate the changes.

postgres@bucardo $ bucardo add sync migration_sync dbs=migration_group herd= migration_rel_group onetimecopy=2 autokick=0
Added sync "migration_sync"

postgres@bucardo $ bucardo list sync
Sync "migration_sync"  Relgroup "migration" [Active]
  DB group "migration_group" pgdb11:target pgdb95:source

Now take a backup of data from source database and copy it to the destination. After data copy is completed, start the replication process.

# Copy the data
pg_dump -v -U postgres -h -d compliance --data-only --disable-triggers -N bucardo | PGOPTIONS='-c session_replication_role=replica' psql compliance
# Start replication
bucardo -U bucardo -d bucardo -P postgres update sync migration_sync autokick=1
# start bucardo
[root@bucardo bucardo]# bucardo start
Checking for existing processes
Removing file "/var/run/bucardo/fullstopbucardo"
Starting Bucardo

Check the bucardo status

[root@bucardo bucardo]# bucardo status
PID of Bucardo MCP: 11239
 Name             State    Last good    Time    Last I/D    Last bad    Time
 migration_sync | Good   | 06:55:33   | 52s   | 0/0       | none      |

# to check the status of the sync
postgres@bucardo $ bucardo status migration_sync
Sync name                : migration_sync
Current state            : No records found
Source relgroup/database : migration / pgdb95
Tables in sync           : 5
Status                   : Active
Check time               : None
Overdue time             : 00:00:00
Expired time             : 00:00:00
Stayalive/Kidsalive      : Yes / Yes
Rebuild index            : No
Autokick                 : Yes
Onetimecopy              : Yes
Post-copy analyze        : Yes
Last error:              :

Once the databases are in sync we can proceed to take the down time. stop the app, check the status of replication again and stop and start the bucardo to replicate the sequences. I observed that sequences are not getting replicated immediately only after restarting bucardo I observed them getting replicated. After that start the application to connect to the new instance.


Can’t locate Sys/ in @INC

[root@localhost perl5]# bucardo start
Checking for existing processes
Can’t locate Sys/ in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/share/perl5/ line 32.
BEGIN failed–compilation aborted at /usr/local/share/perl5/ line 32.
Compilation failed in require at /usr/local/bin/bucardo line 836.

yum install perl-Sys-Syslog


Requires: python3-rpm-macros

While installing python3-devel in centos-7, I got below dependency issue

--> Finished Dependency Resolution
Error: Package: python36-devel-3.6.8-2.el7.ius.x86_64 (ultra-centos-7.x-ius)
           Requires: python3-rpm-macros
Error: Package: python36-devel-3.6.8-2.el7.ius.x86_64 (ultra-centos-7.x-ius)
           Requires: python-rpm-macros
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

I have installed the EPEL but there is no use, finally I found that Centos-Base repository is missing. So I created the repo file “/etc/yum.repo.d/CentOS_7_Base.repo” with content in base repository. Then it worked.

drop multiple tables using do block in PostgreSQL

Sometimes we want to drop all the tables in a database with a particular naming convention, below is the simple SQL script which drop all the tables in a database with naming convention like “test_temp_****”.
query text;
rec record;
query='select schemaname, tablename from pg_tables where tablename like ''test_temp_%''';
FOR rec IN EXECUTE query
RAISE NOTICE 'drop table %.%',rec.schemaname,rec.tablename;
EXECUTE 'drop table ' || rec.schemaname||'.'||rec.tablename;

CentOS 7 repository

Sometime back I removed the default repository file of CentOS, below is the url to download the content of default repository file.

# CentOS-Base.repo
# The mirror system uses the connecting IP address of the client and the
# update status of each mirror to pick mirrors that are updated to and
# geographically close to the client. You should use this for CentOS updates
# unless you are manually picking other mirrors.
# If the mirrorlist= does not work for you, as a fall back you can try the
# remarked out baseurl= line instead.
name=CentOS-$releasever - Base
#released updates

name=CentOS-$releasever - Updates

#additional packages that may be useful
name=CentOS-$releasever - Extras

#additional packages that extend functionality of existing packages
name=CentOS-$releasever - Plus

script to download database log files from aws rds to local linux box

To download all the PostgreSQL error logs from RDS instance to Linux machine use the below script.

for i in `aws rds describe-db-log-files –db-instance-identifier $instancename –query ‘DescribeDBLogFiles[*].[LogFileName]’ –output text`
file_name=`echo $i | cut -d’/’ -f2`
aws rds download-db-log-file-portion –db-instance-identifier  –log-file-name $i –starting-token 0 –output text > $file_name

find java home in linux

This post is only for my use. I have copied this content from stackoverflow

Any of the below command will give the java home in Linux

 java -XshowSettings:properties -version 2>&1 > /dev/null | grep ‘java.home’
 jrunscript -e ‘java.lang.System.out.println(java.lang.System.getProperty(“java.home”))’;
 echo $(readlink -f /usr/bin/java | sed “s:bin/java::”)

psql.bin: FATAL: client certificates can only be checked if a root certificate store is available

After configuration of PostgreSQL SSL, when we try to connect from client so server using psql. we are getting below error.

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 17.0px Menlo; color: #000000; background-color: #ffffff} span.s1 {font-variant-ligatures: no-common-ligatures}

-bash-4.2$ psql -p 5444 -d edb -h
psql.bin: FATAL:  client certificates can only be checked if a root certificate store is available
FATAL:  no pg_hba.conf entry for host “”, user “enterprisedb”, database “edb”, SSL off

This error is because, you have set below entry in pg_hba.conf of data directory.

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 17.0px Menlo; color: #000000; background-color: #ffffff} span.s1 {font-variant-ligatures: no-common-ligatures} span.Apple-tab-span {white-space:pre}

hostssl all all md5 clientcert=1

The clientcert means, client certificate needs to be verified. If we do not set the “ssl_ca_file” like below we will get the error.

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 17.0px Menlo; color: #000000; background-color: #ffffff} span.s1 {font-variant-ligatures: no-common-ligatures}

edb=# show ssl_ca_file ;
(1 row)

After setting this do a restart.


script to take base backup and delete old backup files

# create backup of master Postgess and compress

export PATH=/home/postgres/software/9.6/bin/psql:$PATH
d=$(date +%Y-%m-%d)
export PGPASSWORD=”password”

# Create a directory for today backup

echo “Backup started at $(date) by $0” > $LOG_LOC/$LOGFILE
#Stream mode does not compress
pg_basebackup -h $MASTERD -U back_user -p 5432 -D $BACKUP_LOC/$DIRECTORY –xlog-method=stream -v –checkpoint=fast >> $LOG_LOC/$LOGFILE 2>&1
if [ $? -eq 0 ]
echo “—————–” >> $LOG_LOC/$LOGFILE 2>&1
echo “—————–” >> $LOG_LOC/$LOGFILE 2>&1
echo “Backup completed successfully. Please check the attached logfile” > /tmp/backup_status
echo “Backup failed with some errors. Please check the attached logfile” > /tmp/backup_status
# compress
echo “Compress started at $(date)” >> $LOG_LOC/$LOGFILE
# Create tar file and compress it
tar -czvf $BACKUP_LOC/$d.tar.gz /backup/$d >> /dev/null 2>&1

# clean some old information
/bin/rm -rf /backup/$d >> $LOG_LOC/$LOGFILE 2>&1
echo “Finished at $(date)” >> $LOG_LOC/$LOGFILE

mail -a $LOG_LOC/$LOGFILE -s “Backup status” $TO < /tmp/backup_status
rm -f /tmp/backup_status

## Delete old backups
/usr/bin/find $BACKUP_LOC/*.tar.gz -mtime +28 -delete
/usr/bin/find $LOG_LOC/backup*.log* -mtime +28 -delete
## Delete old archive logs
/usr/bin/find $ARCHIVE_LOC/* -mtime +28 -delete

odd behavior of google cloud machine

I created a machine in google cloud with below.

CPU : 8 vCPUs
RAM : 30 GB
DISK : Standard persistent disk
DB VERSION : PostgreSQL 9.3

When I am checking for time estimation for a 100GB database I am getting different times. There is no processes other then the database and I am the only user using it..

Time for taking base backup first time

plain format : 31m55.348s 

[postgres@old-server bin]$ time pg_basebackup –pgdata=/tmp/BACKUP –format=p –xlog-method=stream

real    31m55.348s
user    0m12.418s
sys     1m45.688s

compressed format : 29m47.282s

[postgres@old-server tmp]$ time pg_basebackup -D /tmp/BACKUP –format=t –compress=9 –gzip
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

real    29m47.282s
user    27m44.429s
sys     0m38.565s

Compressed format takes more time in normal scenarios but here it is reverse, the compressed format is taking less time. 

I have tried the same base backup with xlog-method stream and with out stream. This time the backup time got doubled.

[postgres@old-server ~]$ time pg_basebackup –pgdata=/tmp/BACKUPWithout stream 
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

real    61m2.175s
user    0m16.302s
sys     2m11.380s

[postgres@old-server ~]$ time pg_basebackup –pgdata=/tmp/BACKUP –format=p –xlog-method=stream

real    54m38.801s
user    0m15.629s
sys     2m10.309s

type=’UNKNOWN’ level=’16’ host_id=’-i’ , Oracle Enterprise Manager Express 12c configuration

I am trying to configure Oracle Enterprise Manager Express 12c in sun solaris. When I try to execute below commands to configure.



I the listener is not getting configured to listen on this ports and configuration is not working even though I am getting successful message after the commands. When I check the error log of listener I have seen below messages.

<msg time='2018-05-02T00:34:11.819-04:00' org_id='oracle' comp_id='tnslsnr'
 type=’UNKNOWN’ level=’16’ host_id=’-i’
 02-MAY-2018 00:34:11 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))(SID=******)) * (ADDRESS=(PROTOCOL=tcp)(HOST=**.**.**.**)(PORT=44853)) * establish * M
5O11204 * 12505

<msg time='2018-05-02T00:34:11.820-04:00' org_id='oracle' comp_id='tnslsnr'
 type=’UNKNOWN’ level=’16’ host_id=’-i’
 TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

<msg time='2018-05-02T00:34:15.044-04:00' org_id='oracle' comp_id='tnslsnr'
 type=’UNKNOWN’ level=’16’ host_id=’-i’
 02-MAY-2018 00:34:15 * service_update * M50Ora121 * 0

<msg time='2018-05-02T00:34:21.044-04:00' org_id='oracle' comp_id='tnslsnr'
 type=’UNKNOWN’ level=’16’ host_id=’-i’
 02-MAY-2018 00:34:21 * service_update * M50Ora121 * 0

You can see the host_id is “-i” which is wrong. I have checked the hostname at os level it is showing “-i”.

I changed the hostname using below command

login as root


login as oracle

restart listener

lsnrctl stop
lsnrctl start

sqlplus / as sysdba



Now the Oracle Enterprise Manager Express configured successfully.
« Older Entries Recent Entries »