Monitoring Amazon PostgreSQL RDS with PEM version 6.0

The EDB Postgres Enterprise Manager is an enterprise management tool designed to tune, manage, and monitor a large number of Postgres deployments on servers on-premises or spread out across geographical boundaries. It is the cornerstone of the Management Suite of tools that is integrated into the EDB Postgres Platform from EnterpriseDB® (EDB) and it emerged to solve a great many challenges database administrators faced daily. (The blog, How to Be the Master of Your Database Domain, explored EDB Postgres Enterprise Manager in greater detail.)

EDB customers use EDB Postgres Enterprise Manager for the following key features:

  1. Management of multiple EDB Postgres Advanced Server deployments
  2. Capacity planning
  3. Performance monitoring
  4. Alert Management
  5. Auditing violation
  6. Database Analysis
  7. Postgres Expert

Recently one of EDB’s customers acquired a company that was hosting its data on Amazon PostgreSQL RDS. The customer wanted to leverage EDB Postgres Enterprise Manager to monitor the database deployments on PostgreSQL RDS.

At the time, this request was considered a real possibility. A significant amount of development expertise has gone into EDB Postgres Enterprise Manager to create a sophisticated, enterprise-class tool for managing vast deployments of Postgres. As for remote monitoring, EDB Postgres Enterprise Manager had the capacity to monitor remote databases using an agent on a designated server. EDB Postgres Enterprise Manager also provides capabilities for users, such as database administrators, to create their own custom probes, custom dashboards, and custom alerts. Combined, all of these capabilities mean EDB Postgres Enterprise Manager ultimately provides the kinds of control and flexibility DBAs needed for managing databases that are hosted in different environments.

EDB worked closely with the customer to develop a process for utilizing their subscription for the EDB Postgres Enterprise Manager, which is part of the EDB Postgres Platform, with PostgreSQL databases on Amazon RDS. The steps were formalized into a repeatable process so others could follow suit and enjoy the benefits of EDB Postgres Enterprise Manager in their cloud deployments.

The following are the steps for using EDB Postgres Enterprise Manager with Amazon PostgreSQL RDS:

Amazon PostgreSQL RDS comes with an rds_superuser, which is not the same as a super user in PostgreSQL. Rds_super role has a lot of limitations, and they effect the monitoring capabilities.

To use EDB Postgres Enterprise Manager with PostgreSQL RDS, follow these steps:

  1. Use the following function to modify the EDB Postgres Enterprise Manager probes so that it can leverage user defined views on some catalog tables:
CREATE OR REPLACE FUNCTION strip_pg_catalog_from_probe()
RETURNS boolean
LANGUAGE plpgsql
AS
$function$
 DECLARE
   rec RECORD;
 BEGIN
   CREATE TABLE pem.probe_code_backup AS SELECT id, probe_code FROM pem.probe WHERE id IN (1, 2, 3, 9, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 23, 24, 25, 29, 36, 39, 47, 52, 53, 54);
   FOR rec IN SELECT id, probe_code FROM pem.probe WHERE id IN (1, 2, 3, 9, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 23, 24, 25, 29, 36, 39, 47, 52, 53, 54)
   LOOP
      UPDATE pem.probe SET probe_code=replace(rec.probe_code,'pg_catalog.','') where id=rec.id;
   END LOOP;
   RETURN true;
END;
$function$;
  1. After creating the above function, execute the above function in the EDB Postgres Enterprise Manager server database as shown below:

psql -h localhost –c “SELECT strip_pg_catalog_from_probe()” pem

  1. Connect to PostgreSQL RDS database as rds_super user and execute the following SQL in Postgres and the user’s defined database:
CREATE USER monitor PASSWORD 'password';
GRANT rds_superuser TO monitor;
CREATE SCHEMA rds_pem_views;
SET search_path TO rds_pem_views;
CREATE OR REPLACE VIEW pg_database AS SELECT oid,* FROM pg_catalog.pg_database WHERE datname <> 'rdsadmin';

CREATE OR REPLACE VIEW pg_tablespace AS SELECT oid, * FROM pg_tablespace WHERE spcname <> 'pg_global';

CREATE FUNCTION rds_pem_views.pg_ls_dir(text)
RETURNS TEXT
LANGUAGE sql
AS
$function$
   SELECT ''::TEXT;
$function$;

GRANT ALL ON FUNCTION rds_pem_views.pg_ls_dir(text) TO monitor;
GRANT ALL ON SCHEMA rds_pem_views TO monitor;
GRANT ALL ON  rds_pem_views.pg_database TO monitor;
GRANT ALL ON  rds_pem_views.pg_tablespace TO monitor;

ALTER USER monitor SET search_path TO rds_pem_views,pg_catalog, "$user", public;

The above SQL will create a database user “monitor” with “rds_superuser” privileges.

As mentioned above, rds_superuser in RDS is not the same as a true superuser in PostgreSQL, therefore a schema with a view for pg_database and for pg_tablespace is created.

Views pg_database and pg_tablespace are created because the rds_superuser role has the following restrictions:

  1. Rds_superuser or normal user in PostgreSQL RDS cannot access rdsadmin database;
  2. Rds_superuser or normal user in PostgreSQL RDS cannot calculate the size of pg_global tablespace;
  3. Rds_superuser cannot use pg_ls_dir to list the WAL files in pg_xlog directory.
  1. After executing SQLs, mentioned in step three, we can now use the database user monitor for EDB Postgres Enterprise Manager monitoring.
  2. For remote monitoring of PostgreSQL RDS, use the following link to add the following server:

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.14.html#

When you are following the documentation, please remember to click on “remote monitoring” and choose the EDB Postgres Enterprise Manager agent (PEM Agent) for remote monitoring as shown below:

snap1.png

In the above Bound Agent is pemagent running on PEM Server. Also note that the database is monitor.

After performing the above steps, the EDB Postgres Enterprise Manager server can monitor PostgreSQL RDS, and it can collect all database level statistics. Also, DBAs and other users can configure all database level alerts like autovacuum, etc. EDB Postgres Enterprise Manager provides 63 templates for database level alerts. It also provides flexibility for DBAs and other users to create their own alerts based on custom probes.

Now, some users might have questions or concerns about about system level information from Amazon PostgreSQL RDS, and whether EDB Postgres Enterprise Manager can monitor that data. The answer is yes.

As an example, the following are the steps for configuring EDB Postgres Enterprise Manager to monitor the CPU utilization of PostgreSQL RDS:

  1. Install the Amazon Cloudwatch tools on the EDB Postgres Enterprise Manager Server using following command:
wget http://ec2-downloads.s3.amazonaws.com/CloudWatch-2010-08-01.zip
unzip CloudWatch-2010-08-01.zip

For more information on setting the Amazon CloudWatch command line, please use the following link:

http://docs.aws.amazon.com/AmazonCloudWatch/latest/cli/SetupCLI.html

  1. Create an IAM user on the AWS panel, and attach the managed policy “CloudWatchReadOnlyAccess”.

For more information, please refer to the following link:

http://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/iam-identity-based-access-control-cw.html

  1. Create an AWS Credential File using the following sample:
AWSAccessKeyId=
AWSSecretKey=

For more information on AWS Credential file, please use the following link:

http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html

  1. Change the permission of the above Credential file using the following command:
chmod 600 credential_file
  1. Create a following script to get the CPUUtiliztion information from Amazon CloudWatch:
#!/bin/bash
export AWS_CREDENTIAL_FILE=/usr/local/aws/credential/credential-file-path
export AWS_CLOUDWATCH_HOME=/root/CloudWatch-1.0.20.0
export PATH=$PATH:$AWS_CLOUDWATCH_HOME/bin
export JAVA_HOME=/usr/lib/jvm/jre
export AWS_CLOUDWATCH_URL=http://monitoring.us-west-2.amazonaws.com
echo -e "avg_cpu\tminimum_cpu\tmax_cpu"
mon-get-stats CPUUtilization --namespace="AWS/RDS" --dimensions="DBInstanceIdentifier=postgres" --statistics Average,Minimum,Maximum|tail -n 1|awk '{print $3"\t"$4"\t"$5}'
  1. After creating the above script, make the script executable as given below:
chmod +x rds_cpu.sh

Now you are ready to create a custom probe to use this script for monitoring with EDB Postgres Enterprise Manager.

To create a custom probe, please visit the following link:

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.29.html#pID0E0TBB0HA

Below is a snapshot of a custom probe which I have used:

snap2

By using above probes, user can create their custom alerts in EDB Postgres Enterprise Manager for monitoring CPU utilization on Amazon PostgreSQL RDS. Also they can use data for creating a custom dashboard for RDS.

The following is a snapshot of RDS CPU utilization from EDB Postgres Enterprise Manager.

snap3

If you want to know more about Custom Alerts and Custom Dashboards, please use the following links:

  1. Creating Custom Alerts templates

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.30.html#pID0E0E40HA

  1. Creating Custom Alerts

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.30.html#pID0E0Z60HA

  1. Creating Custom Charts

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.28.html#

  1. Creating Custom Ops Dashboard

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.27.html#pID0E0HFB0HA

 

As you can see, the EDB Postgres Enterprise Manager is very flexible and customizable to meet specific needs.

Data Replication from Oracle logical standby to EDB Postgres

At EnterpriseDB (EDB), we are sometimes presented with individual customer use cases or requests that call for the development of innovative solutions to support their deployment of the EDB Postgres Platform. For example, one of our customers  wanted to leverage Oracle Logical standby for replication to EDB Postgres. And they wanted to know if the EDB Postgres Replication Server supported Oracle logical replication to EDB Postgres.

DBAs and database architects can use the EDB Postgres Replication Server for replicating data from Oracle Logical replication to the EDB Postgres Platform. However, the process of setting up the replication,requires extra steps. Because this was a very interesting use case, I thought a blog containing the recommended steps for using the EDB Postgres Replication Server with Oracle Logical Standby would provide some guidance for many end users. There are instances where this could support strategic replication deployments where EDB Postgres is complementing a legacy solution

For this blog, we will use following IP addresses and hostnames:

  1. EDB Postgres server: 172.17.0.4
  2. Oracle Logical Standby server: 172.17.0.3
  3. Oracle primary server: 172.17.0.2

To use EDB Postgres Replication Server 6.0, users should make sure Oracle Logical Standby is in standby guard status. The following is a list of guard status for Oracle Logical Standby:

 ALTER DATABASE GUARD mode
Mode Detail
ALL (Default) No users (except SYS) can make changes to logical standby data
STANDBY Users may modify logical standby data unless it’s being maintained by local LSPn processes (e.g. via Logical Standby)
NONE Normal security rules are applied; any user with appropriate privileges can modify logical standby data

Following a SQL command, DBAs can use the following to check the guard_status in Oracle:

SQL> SELECT guard_status FROM v$database;
GUARD_S
-------

STANDBY

After confirming the guard status in Oracle Logical Sandby, create a database user with the following privileges:

CREATE USER pubuser IDENTIFIED BY oracle;
GRANT DBA TO pubuser;
GRANT CREATE ANY TRIGGER TO pubuser;
GRANT SELECT ANY TABLE TO pubuser;
GRANT LOCK ANY TABLE TO pubuser;

Let’s create the EDB Postgres Replication Server between Oracle Logical Standby and EDB Postgres. We will be leveraging the EDB Postgres Replication Server command line interface for building publications and subscriptions. If you are not familiar with the EDB Postgres Replication Server command line interface options, please refer to the following link for more information:

https://www.enterprisedb.com/docs/en/6.0/repguide/EDB_Postgres_Replication_Server_Users_Guide.1.59.html#

  1. Add Oracle as the publication database in the EDB Postgres Replication Server. The following command can be used to add the database:
[root@epas95 /]# . /usr/ppas-xdb-6.0/etc/sysconfig/xdbReplicationServer-60.config
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
    -addpubdb \
>   -repsvrfile ${PUBCONF} \
>   -dbtype oracle \
>   -dbhost 172.17.0.3 \
>   -dbport 1521 \
>   -dbuser ${ORAUSER} \
>   -dbpassword ${ORA_ENCRYPT_PASSWD}  \
>   -database ORCL
Adding publication database...
Publication database added successfully. Publication database id:1

Please note the publication database id: 1 and -dbhost: 172.17.0.3 which is the Logical Standby IP Address

  1. Execute the following anonymous PL/SQL block in Oracle Logical Standby as SYSDBA:
BEGIN
 FOR s IN (SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER ='PUBUSER')
 LOOP
         IF DBMS_DDL.IS_TRIGGER_FIRE_ONCE('PUBUSER', s.TRIGGER_NAME)
         THEN
                DBMS_DDL.set_trigger_firing_property( 
                trig_owner => 'PUBUSER',  
                trig_name => s.trigger_name,   
                fire_once => FALSE);
        END IF;
 END LOOP;
END;
/

/

The DBA/User should execute the above PL/SQL block in Oracle Logical Standby to make sure triggers fire when a process changes the base table. By default, in Oracle Logical Standby, triggers never get fired properly in STANDBY guard status.

  1. Add the subscription database in EDB Postgres Replication Server. The following command can be used for adding the subscription database. In our case, we have the EDB Postgres database add:
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
-addsubdb \
>   -repsvrfile ${SUBCONF} \
>   -dbtype enterprisedb \
>   -dbhost 172.17.0.4 \
>   -dbport 5432 \
>   -dbuser ${PGUSER} \
>   -dbpassword ${PG_ENCRYPT_PASSWD}  \
>   -database orcl
Adding Subscription Database...
Subscription database added successfully. Subscription Database id:1006

Please note the subscription database id: 1006 and -dbhost: 172.17.0.3, which is the EDB Postgres server IP address.

  1. Before creating a publication and subscription, first change the GUARD status to NONE in the Oracle Logical Standby as given below:
SQL> ALTER DATABASE GUARD NONE;

Database altered.

The above change is needed because the EDB Postgres Replication Server acquires a LOCK on tables, managed by logical standby, for creating triggers for publication.

  1. Create publication for table: REPLICATION.REPLICATION_TABLE:
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
-createpub replication_table \
>            -repsvrfile ${PUBCONF} \
>            -pubdbid 1 \
>            -reptype t \
>            -tables REPLICATION.REPLICATION_TABLE \
>            -repgrouptype s
Creating publication...
Tables:[[REPLICATION.REPLICATION_TABLE, TABLE]]
Filter clause:[]
Publication created.
  1. After creating the publication, please use the following anonymous block to set the EDB Postgres Replication Server trigger property to fire every time there is a change to the base table.
BEGIN
 FOR s IN (SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER ='PUBUSER')
 LOOP
         IF DBMS_DDL.IS_TRIGGER_FIRE_ONCE('PUBUSER', s.TRIGGER_NAME)
         THEN
                DBMS_DDL.set_trigger_firing_property( 
                trig_owner => 'PUBUSER',  
                trig_name => s.trigger_name,   
                fire_once => FALSE);
        END IF;
 END LOOP;
END;
/
  1. Create subscription for publication: replication_table (created in step 5) using following command:
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
-createsub replication_table \
>   -subsvrfile ${SUBCONF} \
>   -subdbid 1006 \
>   -pubsvrfile ${PUBCONF} \
>   -pubname replication_table
Creating subscription...
Subscription created successfully
  1. After creating the subscription, re-execute the anonymous block to set EDB Postgres Replication Server trigger property:
BEGIN
 FOR s IN (SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER ='PUBUSER')
 LOOP
         IF DBMS_DDL.IS_TRIGGER_FIRE_ONCE('PUBUSER', s.TRIGGER_NAME)
         THEN
                DBMS_DDL.set_trigger_firing_property( 
                trig_owner => 'PUBUSER',  
                trig_name => s.trigger_name,   
                fire_once => FALSE);
        END IF;
 END LOOP;
END;
/
  1. After creating a subscription, rollback the GUARD status to STANDBY in Oracle Logical Standby as shown below:
SQL> ALTER DATABASE GUARD STANDBY;

Database altered.

Now, we are ready to replicate data from Oracle Logical Standby to EDB Postgres.

Let’s first take the snapshot of data from Oracle Logical Standby to EDB Postgres using the following command:

[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar\
-dosnapshot replication_table \
-repsvrfile ${SUBCONF} \
-verboseSnapshotOutput true
Performing snapshot...
Running EnterpriseDB Migration Toolkit (Build 49.1.5) ...
Source database connectivity info...
conn =jdbc:oracle:thin:@172.17.0.3:1521:ORCL
user =pubuser
password=******
Target database connectivity info...
conn =jdbc:edb://172.17.0.4:5432/orcl?loginTimeout=60&connectTimeout=30
user =pubuser
password=******
Connecting with source Oracle database server...
Connected to Oracle, version 'Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options'
Connecting with target EnterpriseDB database server...
Connected to EnterpriseDB, version '9.5.5.10'
Importing redwood schema REPLICATION...
Table List: 'REPLICATION_TABLE'
Loading Table Data in 8 MB batches...
Disabling FK constraints & triggers on replication.replication_table before truncate...
Truncating table REPLICATION_TABLE before data load...
Disabling indexes on replication.replication_table before data load...
Loading Table: REPLICATION_TABLE ...
[REPLICATION_TABLE] Migrated 10 rows.
[REPLICATION_TABLE] Table Data Load Summary: Total Time(s): 0.04 Total Rows: 10
Enabling FK constraints & triggers on replication.replication_table...
Enabling indexes on replication.replication_table after data load...
Performing ANALYZE on EnterpriseDB database...
Data Load Summary: Total Time (sec): 0.191 Total Rows: 10 Total Size(MB): 0.0
Schema REPLICATION imported successfully.
Migration process completed successfully.
Migration logs have been saved to /var/log/xdb-6.0
******************** Migration Summary ********************
Tables: 1 out of 1
Total objects: 1
Successful count: 1
Failed count: 0
Invalid count: 0
*************************************************************
Snapshot taken successfully.

After taking an initial snapshot, let’s modify the replication table from Oracle primary:

18-JAN-17 REPLICATION@ ORCL PRIMARY> UPDATE replication_table SET id=11 WHERE id=10;
Elapsed: 00:00:00.01
18-JAN-17 REPLICATION@ ORCL PRIMARY> COMMIT;
Elapsed: 00:00:00.01
18-JAN-17 REPLICATION@ ORCL PRIMARY> SELECT * FROM replication_table;
1 FIRST
2 SECOND
3 THIRD
4 FOURTH
5 FIFTH
6 SIXTH
7 SEVENTH
8 EIGHTH
9 NINTH
11 TENTH

Elapsed: 00:00:00.00

After making the changes on Oracle primary, we can execute SELECT command on Oracle Logical Standby to verify if the data change was replicated by Oracle in logical standby mode.

18-JAN-17 REPLICATION@ ORCL STANDBY> SELECT * FROM replication_table;
1 FIRST
2 SECOND
3 THIRD
4 FOURTH
5 FIFTH
6 SIXTH
7 SEVENTH
8 EIGHTH
9 NINTH
11 TENTH

10 rows selected.

Elapsed: 00:00:00.00
  1. Now perform the manual sync to verify EDB Postgres Replication Server can replicate the above changes in EDB Postgres.
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar  -dosynchronize  replication_table -repsvrfile ${SUBCONF} -repgrouptype s
Performing synchronize...
Synchronize done successfully.
[root@epas95 /]# psql orcl
psql.bin (9.5.5.10)
Type "help" for help.
orcl=# select * from replication.replication_table ;
id |   col
----+---------
1 | FIRST
2 | SECOND
3 | THIRD
4 | FOURTH
5 | FIFTH
6 | SIXTH
7 | SEVENTH
8 | EIGHTH
9 | NINTH
11 | TENTH

(10 rows)

The above snapshot shows that after performing -dosynchronize, the EDB Postgres Replication Server was able to replicate incremental changes on Oracle Logical Standby to EDB Postgres.

The pg_rewind improvement in PostgreSQL 9.6

The pg_rewind tool was first introduced in PostgreSQL 9.5. This is best used in a situation when a standby becomes a master, then the old master can be reattached to the new master as a standby without restoring it from the new base backup.

The tool examines the timeline histories of the new master (the old standby) and the old master to determine the point where they diverged, and it expects to find Write-Ahead Logs (WAL) in the old master cluster’s pg_xlog directory reaching all the way back to the point of divergence.

For pg_rewind, DBAs need to have the following:

  1. wal_log_hints = on or PostgreSQL cluster with data checksum.

DBAs can also have data checksums and wal_log_hints, both enabled in their PostgreSQL cluster.

For more information on wal_log_hints and enabling data checksum, the following links are useful:

https://www.postgresql.org/docs/9.5/static/runtime-config-wal.html#GUC-WAL-LOG-HINTS

https://www.postgresql.org/docs/9.5/static/app-initdb.html#APP-INITDB-DATA-CHECKSUMS

  1. full_page_writes should be enabled, which is by default enabled in Postgres. For more information on this parameter, see the following link:

https://www.postgresql.org/docs/9.5/static/runtime-config-wal.html#GUC-FULL-PAGE-WRITES

In Postgres 9.6, Alexander Korotkov has introduced a new feature in the pg_rewind tool. Details of the commit are given below:

 commit e50cda78404d6400b1326a996a4fabb144871151
 Author: Teodor Sigaev <teodor@sigaev.ru>
 Date:   Tue Dec 1 18:56:44 2015 +0300
 
      Use pg_rewind when old master timeline was switched
 
      Allow pg_rewind to work when old master timeline was switched. Now
      user can return promoted standby to old master.
  
      old master timeline history becomes a global variable. Index
      in old master timeline history is used in function interfaces instead of
      specifying TLI directly. Thus, SimpleXLogPageRead() can easily start
      reading XLOGs from next timeline when current timeline ends.
  
      Author: Alexander Korotkov
      Review: Michael Paquier

With the new feature, a DBA/user can now reattach the standby that got promoted due to the following reasons:

  1. User error:

By mistake, the user promoted the standby using pg_ctl promote option or by creating a trigger

  1. Failover script has some logic problem, which resulted in promotion of standby to Master
  2. A user is testing failover scenario. (In failover testing, the standby needs to be promoted and needs to bring back to a state where standby can be safely attached to PostgreSQL master)
  3. Bug in failover management tool (a typical scenario is due to a network blip and the failover management tool promoted the standby)

There could be other possible scenarios where this can be useful.

Let’s try with PostgreSQL 9.5 to see the previous behavior of pg_rewind in a use case, where the DBA wants to reattach the standby to the primary server after promoting it.

For this test, we have two PostgreSQL clusters: node1 and node2. Node1 (port: 54445) is a master/primary. Node2 (port: 54446) is a standby which streams data from master node1 (port 54445)
Below is streaming replication status:

bash-postgres-9.5 $ psql -p 54445 -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+---------------------------------
pid              | 4482
usesysid         | 10
usename          | enterprisedb
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 46780
backend_start    | 21-DEC-16 18:15:08.442887 +00:00
backend_xmin     | 
state            | streaming
sent_location    | 0/8000060
write_location   | 0/8000060
flush_location   | 0/8000060
replay_location  | 0/8000060
sync_priority    | 0
sync_state       | async


bash-postgres-9.5 $  psql -p 54446 -c "SELECT pg_is_in_recovery()"
 pg_is_in_recovery 
-------------------
 t
(1 row)

As you can see the PostgreSQL instance running on port 54446 is a standby (i.e it’s in recovery mode) and it’s streaming data from node1 (port 54445).

Now, let’s promote node2 and check the status of replication on node1.

bash-postgres-9.5 $  touch /tmp/test.trigger
bash-postgres-9.5 $  psql -p 54446 -c "SELECT pg_is_in_recovery()"
 pg_is_in_recovery 
-------------------
 f
(1 row)

bash-postgres-9.5 $  psql -p 54445 -x -c "select * from pg_stat_replication;"
(0 rows)

The above snapshot shows that node2(port 54446) is now out of recovery mode. And replication status on node1(port 54445) shows that node2 is not streaming data from node1.

Now, let’s try to use pg_rewind to reattach node2 (port 54446) as standby to node1 (port 54445) to verify behavior in version 9.5.

bash-postgres-9.5 $ pg_ctl -D /var/lib/ppas/node2/ stop
bash-postgres-9.5 $  pg_rewind -D /var/lib/ppas/node2  --source-server="port=54445 host=127.0.0.1" 
could not find common ancestor of the source and target cluster's timelines
Failure, exiting

The above messages show that the user cannot reattach a promoted standby to the old master, which is a limitation of pg_rewind in 9.5, and it’s an expected behavior of pg_rewind 9.5.

We can now try the same scenario with PostgreSQL 9.6 to check the pg_rewind improvement.

bash-postgres-9.6 $ pg_ctl --version
pg_ctl (EnterpriseDB) 9.6.1.4

bash-postgres-9.6$ psql -p 54445 -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+---------------------------------
pid              | 4838
usesysid         | 10
usename          | enterprisedb
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 46790
backend_start    | 21-DEC-16 18:35:03.216873 +00:00
backend_xmin     | 
state            | streaming
sent_location    | 0/4000060
write_location   | 0/4000060
flush_location   | 0/4000060
replay_location  | 0/4000060
sync_priority    | 0
sync_state       | async

bash-postgres-9.6$ psql -p 54446 -x -c "select pg_is_in_recovery();"
-[ RECORD 1 ]-----+--
pg_is_in_recovery | t

The above snapshot shows the streaming replication between node1 (port 54445) to node2 (port 54446).

Now, let’s promote node2. Below is a snapshot of promoting node2:

bash-postgres-9.6$ touch /tmp/test
bash-postgres-9.6$ psql -p 54446 -x -c "select pg_is_in_recovery();"
-[ RECORD 1 ]-----+--
pg_is_in_recovery | f


bash-postgres-9.6$ psql -p 54446 -x -c "select * from pg_stat_replication;"
(0 rows)

The above snapshot shows that node2 (port 54446) is out of recovery mode and there is no replication between node1(port 54445) and node2(port 54446).

Let’s try to use pg_rewind to re-attach node2 as standby with node1.

bash-postgres-9.6$ pg_ctl -D /var/lib/ppas/node2/ stop
waiting for server to shut down.... done
server stopped
bash-postgres-9.6$ pg_rewind -D /var/lib/ppas/node2 --source-server="port=54445 host=127.0.0.1" 
servers diverged at WAL position 0/4000140 on timeline 1
rewinding from last common checkpoint at 0/4000098 on timeline 1
Done!

The above snapshot shows that we were able to pg_rewind node2 (port 54446) to the last common checkpoint of node1(port 54445).

We can now start node2 and check the status of replication. Below is a snapshot of that activity:

bash-postgres-9.6$ pg_ctl -D /var/lib/ppas/node2/ start
server starting
-bash-4.1$ 2016-12-21 18:55:15 UTC LOG:  redirecting log output to logging collector process
2016-12-21 18:55:15 UTC HINT:  Future log output will appear in directory "pg_log".

bash-postgres-9.6$ psql -p 54445 -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+---------------------------------
pid              | 5019
usesysid         | 10
usename          | enterprisedb
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 46794
backend_start    | 21-DEC-16 18:55:15.635726 +00:00
backend_xmin     | 
state            | streaming
sent_location    | 0/4012BA0
write_location   | 0/4012BA0
flush_location   | 0/4012BA0
replay_location  | 0/4012BA0
sync_priority    | 0
sync_state       | async

bash-postgres-9.6$ psql -p 54446 -x -c "select pg_is_in_recovery();"
-[ RECORD 1 ]-----+--
pg_is_in_recovery | t

Partition pruning in EDB Postgres 9.5

One of my colleague who was recently working with a customer has presented a customer case. According to him, customer has a partitioned table and EDB Postgres was not applying the partition pruning in his query. So, I thought to blog about partition pruning, so that EDB Postgres developers and DBAs can benefit.

EDB Postgres supports two types of partition pruning:

Constraint exclusion pruning:

It is a feature introduced in Postgresql 8.1. This type of pruning works with PostgreSQL-style of partition. With constraint exclusion enabled, the planner will examine the constraints of each partition and try to prove that the partition need not be scanned because it could not contain any rows meeting the query’s WHERE clause. When the planner can prove this, it excludes the partition from the query plan.

However, it has some limitations. Following is the limitation of constraint_exclusion:

a. Constraint exclusion only works when the query’s WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time.
b. Keep the partitioning constraints simple, else the planner may not be able to prove that partitions don’t need to be visited. Use simple equality conditions for list partitioning, or simple range tests for range partitioning, as illustrated in the preceding examples. A good rule of thumb is that partitioning constraints should contain only comparisons of the partitioning column(s) to constants using B-tree-indexable operators.

For verification, below shows the behavior of constraint_exclusion pruning:
1. Let’s create PostgreSQL-style partition table using table inheritance feature.

CREATE TABLE measurement (
     city_id        int not null,
     logdate        date not null,
     peaktemp        int,
     unitsales      int
 );
CREATE TABLE measurement_y2004m02 (
     CHECK ( date_part('month'::text, logdate) = 2)
 ) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 (
     CHECK ( date_part('month'::text, logdate) = 3 )
 ) INHERITS (measurement);

  1. Execute simple query to verify the constraint_exclusion behavior based on above definition:
 edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE date_part('month'::text, logdate) = 3;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on measurement
               Filter: (date_part('month'::text, logdate) = '3'::double precision)
         ->  Seq Scan on measurement_y2004m02
               Filter: (date_part('month'::text, logdate) = '3'::double precision)
         ->  Seq Scan on measurement_y2004m03
               Filter: (date_part('month'::text, logdate) = '3'::double precision)
(8 rows)

Above output of the query shows EDB Postgres considered all partitions of table measurements, even though we had included partition column and constant value in WHERE clause. This is due to check constraint which has date_part function. date_part function is not immutable in Postgres, therefore at planning time, EDB Postgres doesn’t know what value it will return. And, if user doesn’t include proper WHERE clause as mentioned in check constraint, pruning will not work.

In Postgres you can make a function immutable by using ALTER FUNCTION command.

In below example, we will make date_part function immutable to check if constraint_exclusion works with date_part immutable function or not:

  1. Convert date_part function to immutable :
edb=# ALTER FUNCTION date_part (text, timestamp without time zone ) immutable;
ALTER FUNCTION
  1. Perform EXPLAIN command to check the behavior of constraint_exclusion using immutable function:
edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE date_part('month'::text, logdate) = 3;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on measurement
               Filter: (date_part('month'::text, logdate) = '3'::double precision)
         ->  Seq Scan on measurement_y2004m03
               Filter: (date_part('month'::text, logdate) = '3'::double precision)
(6 rows)

As you can see with immutable function EDB Postgres was able to perform constraint_exclusion pruning.

What if we change the WHERE clause little bit and include < and = operator in our SQL queries (below are examples)

edb=#  EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate < DATE '2004-03-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone)          ->  Seq Scan on measurement_y2004m02
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone)          ->  Seq Scan on measurement_y2004m03
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone) (8 rows) edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate = DATE '2004-02-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate = '01-FEB-04 00:00:00'::timestamp without time zone)
         ->  Seq Scan on measurement_y2004m02
               Filter: (logdate = '01-FEB-04 00:00:00'::timestamp without time zone)
         ->  Seq Scan on measurement_y2004m03
               Filter: (logdate = '01-FEB-04 00:00:00'::timestamp without time zone)
(8 rows)

As you can see with a change in WHERE clause and exclusion of the way constraint defined on partition, Postgres will scan all partitions.

Based on above we can conclude that if a user is planning to use Postgres way of partition then they have to be careful about the constraint definition in order to utilize constraint_exclusion pruning.

Lets modify the definition of measurement table and verify the ,=, <= and = operator in WHERE clause.

CREATE TABLE measurement (
     city_id        int not null,
     logdate        date not null,
     peaktemp        int,
     unitsales      int
 ); 
CREATE TABLE measurement_y2004m02 (
     CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )  ) INHERITS (measurement); CREATE TABLE measurement_y2004m03 (      CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
 ) INHERITS (measurement);

Below is explain plan based on above definition:

edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate < DATE '2004-03-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone)          ->  Seq Scan on measurement_y2004m02
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone) (6 rows) edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate = DATE '2004-03-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate = '01-MAR-04 00:00:00'::timestamp without time zone)
         ->  Seq Scan on measurement_y2004m03
               Filter: (logdate = '01-MAR-04 00:00:00'::timestamp without time zone)
(6 rows)

Above clearly shows that with correct constraint definition, constriant_exclusion pruning can work for >,<,>=, <= and = operator in WHERE clause.

Fast pruning:

EDB Postgres has CREATE TABLE PARTITION SYNTAX since version 9.1. PARTITION SYNTAX in EDB Postgres uses one more pruning called fast pruning. Fast pruning uses the partition metadata and query predicates to efficiently reduce the set of partitions to scan. Fast pruning in EDB Postgres happens before query plan. Let’s verify the behavior of fast pruning.
As mentioned fast pruning works with partition which user created using EDB Postgres CREATE TABLE PARTITION Syntax. Let’s modify the above definition of measurement table to use CREATE TABLE PARTITION SYNTAX as given below:

CREATE TABLE  measurement (
     city_id        int not null,
     logdate        date not null,
     peaktemp        int,
     unitsales      int
 )
PARTITION BY RANGE(logdate)
(PARTITION y2004m01 VALUES LESS THAN ('2004-02-01'),
 PARTITION y2004m02 VALUES LESS THAN ('2004-03-01'),
 PARTITION y2004m03 VALUES LESS THAN ('2004-04-01')
);
edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate < DATE '2004-03-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone)          ->  Seq Scan on measurement_y2004m01
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone)          ->  Seq Scan on measurement_y2004m02
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone) (8 rows) edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate = DATE '2004-03-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate = '01-MAR-04 00:00:00'::timestamp without time zone)
         ->  Seq Scan on measurement_y2004m03
               Filter: (logdate = '01-MAR-04 00:00:00'::timestamp without time zone)
(6 rows)

edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate > DATE '2004-03-01';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate > '01-MAR-04 00:00:00'::timestamp without time zone)
         ->  Seq Scan on measurement_y2004m03
               Filter: (logdate > '01-MAR-04 00:00:00'::timestamp without time zone)
(6 rows)

For more information on EDB Postgres pruning please refer following link:
https://www.enterprisedb.com/docs/en/9.5/oracompat/Database_Compatibility_for_Oracle_Developers_Guide.1.327.html#

Compiling PLV8 with Postgres Plus Advanced Server

PLV8 is a programming language that lets users write stored procedures and triggers in JavaScript and store them in their Postgres database. This allows application programmers to write a lot of their server-side programming in the same language they use to build their web client applications. Fewer languages to learn usually means fewer mistakes and faster time to completion. The extensive language support is one of many reasons why Postgres’ use across the world is increasing lately. The recent addition of document data support with JSON and JSONB data types in PostgreSQL, and in Postgres Plus Advanced Server from EnterpriseDB, is the main reason for the increasing interest in the PL/V8 language extension.

Below are the steps you need to compile PLV8 with Postgres Plus Advanced Server 9.3/9.4.

To get started, here are the prerequisites:
1. A supported version of PostgreSQL or Postgres Plus Advanced Server, such as versions 9.1 and higher.
2. V8 version 3.14.5
3. g++ version 4.5.1

If you want to know more about V8, you can visit the following wiki page:
http://en.wikipedia.org/wiki/V8_(JavaScript_engine)

It’s important to note that when compiling PLV8 with Postgres Plus Advanced Server 9.3 or the upcoming 9.4, you will get the following two types of error messages:

The first error:

[root@localhost plv8js]# make
sed -e 's/^#undef PLV8_VERSION/#define PLV8_VERSION "1.5.0-dev1"/' plv8_config.h.in > plv8_config.h
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES  -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -fPIC -c -o plv8.o plv8.cc
plv8.cc: In function ‘void _PG_init()’:
plv8.cc:226: error: invalid conversion from ‘void (*)(XactEvent, void*)’ to ‘void (*)(XactEvent, void*, bool)’
plv8.cc:226: error:   initializing argument 1 of ‘void RegisterXactCallback(void (*)(XactEvent, void*, bool), void*)’
make: *** [plv8.o] Error 1

The above error message is a result of a different signature of

typedef void (*XactCallback)

in the Advanced Server transaction system.

To fix the above issue, the user can replace the following in plv8.cc:

static void plv8_xact_cb(XactEvent event, void *arg);

With

static void plv8_xact_cb(XactEvent event, void *arg, bool spl_context);

The second error:
After making the above changes, you may get the following error after trying to compile the source code using the “make” command:

[root@localhost plv8js]# make
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES  -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -fPIC -c -o plv8.o plv8.cc
plv8.cc:137: warning: ‘void plv8_xact_cb(XactEvent, void*, bool)’ used but never defined
plv8.cc:232: warning: ‘void plv8_xact_cb(XactEvent, void*)’ defined but not used
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES  -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -fPIC -c -o plv8_type.o plv8_type.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES  -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -fPIC -c -o plv8_func.o plv8_func.cc
In file included from plv8_param.h:11,
                 from plv8_func.cc:9:
/usr/ppas-9.4/include/server/nodes/params.h:77: error: expected ‘,’ or ‘...’ before ‘typeid’
make: *** [plv8_func.o] Error 1

The above is mainly due to the use of typeid in params.h; typeid is the reserved keyword of C++ compiler.

To fix this issue, make the following changes in plv8.h

extern "C" {
#include "postgres.h"

#include "access/htup.h"
#include "fmgr.h"
#include "mb/pg_wchar.h"
#include "utils/tuplestore.h"
#include "windowapi.h"
}

with

#define typeid __typeid
extern "C" {
#include "postgres.h"

#include "access/htup.h"
#include "fmgr.h"
#include "mb/pg_wchar.h"
#include "utils/tuplestore.h"
#include "windowapi.h"
}
#undef typeid

In plv8_param.h, change the following:

extern "C" {
#include "postgres.h"

/*
 * Variable SPI parameter is since 9.0.  Avoid include files in prior versions,
 * as they contain C++ keywords.
 */
#include "nodes/params.h"
#if PG_VERSION_NUM >= 90000
#include "parser/parse_node.h"
#endif	// PG_VERSION_NUM >= 90000

} // extern "C"

With

#define typeid __typeid
extern "C" {
#include "postgres.h"

/*
 * Variable SPI parameter is since 9.0.  Avoid including files in prior versions,
 * as they contain C++ keywords.
 */
#include "nodes/params.h"
#if PG_VERSION_NUM >= 90000
#include "parser/parse_node.h"
#endif	// PG_VERSION_NUM >= 90000

} // extern "C"
#undef typeid

In plv8_param.cc, replace following:

extern "C" {

#include "catalog/pg_type.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"

} // extern "C"

with

#define typeid __typeid
extern "C" {

#include "catalog/pg_type.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"

} // extern "C"
#undef typeid

After making the above changes, you will be able to compile PLV8 with Advanced Server as shown below:

[root@localhost plv8js]# make
sed -e 's/^#undef PLV8_VERSION/#define PLV8_VERSION "1.5.0-dev1"/' plv8_config.h.in > plv8_config.h
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES  -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -fPIC -c -o plv8.o plv8.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES  -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -fPIC -c -o plv8_type.o plv8_type.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES  -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -fPIC -c -o plv8_func.o plv8_func.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES  -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -fPIC -c -o plv8_param.o plv8_param.cc
echo "extern const unsigned char coffee_script_binary_data[] = {" >coffee-script.cc
(od -txC -v coffee-script.js | \
	sed -e "s/^[0-9]*//" -e s"/ \([0-9a-f][0-9a-f]\)/0x\1,/g" -e"\$d" ) >>coffee-script.cc
echo "0x00};" >>coffee-script.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES  -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -fPIC -c -o coffee-script.o coffee-script.cc
echo "extern const unsigned char livescript_binary_data[] = {" >livescript.cc
(od -txC -v livescript.js | \
	sed -e "s/^[0-9]*//" -e s"/ \([0-9a-f][0-9a-f]\)/0x\1,/g" -e"\$d" ) >>livescript.cc
echo "0x00};" >>livescript.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES  -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -fPIC -c -o livescript.o livescript.cc
g++ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o plv8.so plv8.o plv8_type.o plv8_func.o plv8_param.o coffee-script.o livescript.o -L/usr/ppas-9.4/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/ppas-9.4/lib',--enable-new-dtags  -lv8 
sed -e 's/@PLV8_VERSION@/1.5.0-dev1/g' plv8.control.common | g++ -E -P -DLANG_plv8 - > plv8.control
sed -e 's/@LANG_NAME@/plv8/g' plv8.sql.common | g++ -E -P -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -DLANG_plv8 - > plv8--1.5.0-dev1.sql
sed -e 's/@PLV8_VERSION@/1.5.0-dev1/g' plv8.control.common | g++ -E -P -DLANG_plcoffee - > plcoffee.control
sed -e 's/@LANG_NAME@/plcoffee/g' plv8.sql.common | g++ -E -P -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -DLANG_plcoffee - > plcoffee--1.5.0-dev1.sql
sed -e 's/@PLV8_VERSION@/1.5.0-dev1/g' plv8.control.common | g++ -E -P -DLANG_plls - > plls.control
sed -e 's/@LANG_NAME@/plls/g' plv8.sql.common | g++ -E -P -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -DLANG_plls - > plls--1.5.0-dev1.sql
/bin/mkdir -p '/usr/ppas-9.4/lib'
/bin/mkdir -p '/usr/ppas-9.4/share/extension'
/bin/mkdir -p '/usr/ppas-9.4/share/extension'
/usr/bin/install -c -m 755  plv8.so '/usr/ppas-9.4/lib/plv8.so'
/usr/bin/install -c -m 644 plv8.control '/usr/ppas-9.4/share/extension/'
/usr/bin/install -c -m 644 plv8.control plv8--1.5.0-dev1.sql plcoffee.control plcoffee--1.5.0-dev1.sql plls.control plls--1.5.0-dev1.sql '/usr/ppas-9.4/share/extension/'

After compiling PLV8, you now can install the PLV8 language in Advanced Server using the following command:

beta=# CREATE EXTENSION PLV8;
CREATE EXTENSION
beta=# 

To test your installed PLV8, here is some sample code:

beta=# DO $$ PLV8.elog(NOTICE, 'this', 'is', 'inline', 'code') $$ LANGUAGE PLV8;
NOTICE:  this is inline code
DO

beta=# CREATE TYPE rec AS (i integer, t text);
CREATE TYPE
beta=# CREATE FUNCTION set_of_records() RETURNS SETOF rec AS
beta-# $$
beta$#     // PLV8.return_next() stores records in an internal tuplestore,
beta$#     // and return all of them at the end of function.
beta$#     PLV8.return_next( { "i": 1, "t": "a" } );
beta$#     PLV8.return_next( { "i": 2, "t": "b" } );
beta$# 
beta$#     // You can also return records with an array of JSON.
beta$#     return [ { "i": 3, "t": "c" }, { "i": 4, "t": "d" } ];
beta$# $$
beta-# LANGUAGE PLV8;
CREATE FUNCTION
beta=# SELECT * FROM set_of_records();
 i | t 
---+---
 1 | a
 2 | b
 3 | c
 4 | d
(4 rows)

In case you need a patched version of PLV8, use the following git repository:

 https://github.com/vibhorkum/PLV8_FOR_PPAS.git plv8_ppas

To use this, execute the following command:

 git clone https://github.com/vibhorkum/PLV8_FOR_PPAS.git plv8_ppas
cd PLV8_ppas
make
make install

To test the compiled PLV8, you can use the following command:

[root@localhost plv8_ppas]# make installcheck
/usr/ppas-9.4/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --psqldir='/usr/ppas-9.4/bin'    --dbname=contrib_regression init-extension plv8 inline json startup_pre startup varparam json_conv window dialect
(using postmaster on Unix socket, port 5444)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test init-extension           ... ok
test plv8                     ... ok
test inline                   ... ok
test json                     ... ok
test startup_pre              ... ok
test startup                  ... ok
test varparam                 ... ok
test json_conv                ... ok
test window                   ... ok
test dialect                  ... ok

======================
 All 10 tests passed. 
======================

Meet BART – A New Tool for Backup And Recovery Management

EnterpriseDB recently launched a new tool for backup and recovery – named simply EDB Backup and Recovery Tool, or BART. This tool makes the DBA’s life easier by simplifying the tasks for managing their Postgres physical backup and recovery tasks, whether they are PostgreSQL or Postgres Plus Advanced Server deployments.

BART has the following advantages over custom scripts for managing backups:

1. It’s stable and it uses the tool pg_basebackup to take a physical backup. This tool has been well defined and is well-supported by the PostgreSQL community.

2. It catalogs all of the backups users are taking, which is important in terms of:
    i. Listing the type of backups used
   ii. Listing the status of those backups with server information.

3. BART also provides functionality to restore backups, with all required archived WAL files. So automation around this tool will make DBAs’ lives easier for restore and recovery.

4. BART provides an option to validate your backup by using checksum. This is useful for confirming you took a valid backup and it is not corrupted at disk level.

5. BART provides an option to define your retention policy around the backups you are keeping.

Given all of the above advantages, I decided to give this new tool a try and share some tips. To get started, you need the following prerequisites:

1. BART currently requires a Linux 64 bit platform, CentOS 6.x or RHEL 6.x
2. Need to have password-less, direct SSH access to the target machine where you want to restore backups as well as the database servers you want backed up
3. Install the Postgres Plus Advanced Server or PostgreSQL binaries for pg_basebackup

Yum or rpm

To install this tool, you have two options that I will explore below:

1. Yum command
2. Rpm command.

Using the yum command:

To perform a yum command installation, BART users can ask EDB for credentials to the EnterpriseDB yum repository and configure the their local yum repository as follows:

echo &quot;[tools]
name=EnterpriseDB Tools
baseurl=http://username:password@yum.enterprisedb.com/tools/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=0&quot; &gt; /etc/yum.repos.d/edbtools.repo

After creating the yum repo, the user can execute the following command to install BART:

 yum install edb-bart

If the user doesn’t want to install the EDB Backup and Recovery Tool using the yum command, then the user can download a free standing rpm using the link below from EDB’s website:

http://www.enterprisedb.com/downloads/postgres-postgresql-downloads

and then enter the rpm install command as follows:

rpm -ivh edb-bart-1.0.1-1.rhel6.x86_64.rpm

After installing BART using the above commands, the user can see the binaries in the directory:/usr/edb-bart-1.0/bin and a sample BART configuration file in /usr/edb-bart-1.0/etc

That’s a very easy installation.

For more information on configuring BART Host and Database Host, the following are some documents that will help:
1. pg_basebackup configuration for PostgreSQL:
http://www.postgresql.org/docs/current/static/app-pgbasebackup.html

2. For direct password less ssh configuration user can refer following link
http://www.enterprisedb.com/docs/en/1.0/bart/EDB_Backup_and_Recovery_Tool_Guide-17.htm#P1008_76316

After the installation of the BART binaries, the user also has to create a BART configuration file.

The following is a sample configuration file for BART:

[BART]
bart-host= enterprisedb@127.0.0.1
backup_path = /opt/backup
pg_basebackup_path = /usr/ppas-9.4/bin/pg_basebackup
logfile = /tmp/bart.log

[PG]
host = 127.0.0.1
port = 5432
user = postgres
description = &quot;Postgres server&quot;

[PPAS94]
host = 127.0.0.1
port = 5444
user = enterprisedb
description = &quot;PPAS 94 server&quot;

Global Configuration Settings

Content under the [BART] tag are called global configuration settings. Under this tag are the following:

1. bart-host: the IP address of the host on which BART is installed. The value for this parameter must be specified in the form: bart_user@bart_host_address, where bart_user is the operating system user account on the BART host that is used to run BART and owns the BART backup catalog directory. bart_host_address is the IP address of the BART host.

2. backup_path: specifies the file system parent directory where all BART database server base backups and archived WAL files are stored. This parameter is required.

3. pg_basebackup_path: specifies the path to the pg_basebackup program of the Postgres database server installed on the BART host.

4. log file: specifies the path to the BART log file. This parameter is optional. If no path to a log file is specified after logfile =, or if the parameter is commented out, BART does not create a log file.

The remaining part of configuration file is self-explanatory. The TAG: [PG]/[PPAS94] part is content for servers which the user wants to back up.

Pg_basebackup Settings

After performing the above configuration on the Backup Server, the user has to do set following settings on the servers that they want to back up. Below are the settings for enabling backup using pg_basebackup.

The user has to set a few parameters in PostgreSQL postgresql.conf file, which he wants to backup:

1. wal_level parameter to archive or hot_standby.
2. archive_mode=on
3. archive_command setting.
4. max_wal_senders to 1 or more than one, since pg_basebackup uses the replication protocol to copy data directory.

For more information on each setting please refer to the following:
1. wal_level:
http://www.postgresql.org/docs/9.4/static/runtime-config-wal.html

2. archive_mode and archive_command:
http://www.postgresql.org/docs/9.4/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING

3. max_wal_senders:
http://www.postgresql.org/docs/9.4/static/runtime-config-replication.html
http://www.enterprisedb.com/docs/en/1.0/bart/EDB_Backup_and_Recovery_Tool_Guide-19.htm#TopOfPage

With the above settings, the user then needs to update the pg_hba.conf file for the replication connection.

Note: The above settings are for pg_basebackup to take backups using replication protocols. In case users need more information about pg_basebackup and settings, please use the above mentioned link

How BART Works

Now, since we have configured both servers, let’s have a look how BART works.

The following command executes a backup:

 bart -c bart.cfg BACKUP -s ppas94

And below is the output:

[bart@localhost ~]$ bart -c bart.cfg BACKUP -s ppas94

INFO:  creating backup for server 'ppas94'
INFO:  backup identifier: '1413852137762'
6394456/6394456 kB (100%), 1/1 tablespace

INFO:  backup checksum: 7f49ea9653511308710c174f22ec765d
INFO:  backup completed successfully
[bart@localhost ~]$ 

That was an easy way to take a backup. The DBA can also create a job to execute the above command to take backups.

If the user wants to list the backup using BART, the user can use the option SHOW-BACKUPS:

[bart@localhost ~]$ bart -c bart.cfg SHOW-BACKUPS -s ppas94
 Server Name   Backup ID       Backup Time           Backup Size  
                                                                  
 ppas94        1413852137762   2014-10-20 17:43:41   6244.59 MB   

This is useful for knowing what backups a user has available for recovery. The above command gives important information:

1.	Backup ID: It’s a unique ID for the physical backup
2.	Backup Time: Time when backup was taken
3.	Backup Size: Size of backup

This information is useful when a user wants to plan for recovery using backup. This way, the user can also plan for disk size.

Sometimes a user wants to verify their backup state. VERIFY-CHKSUM option is useful in this case:

[bart@localhost ~]$ bart -c bart.cfg VERIFY-CHKSUM -s ppas94 -i 1413852137762
 Server Name   Backup ID       Verify  
                                       
 ppas94        1413852137762   OK      

I have to say, after putting EDB BART through its paces, I think DBAs will enjoy having such a great tool for making Backup Management easy.

In my next post, I will blog about the Recovery process.

Switchover/Switchback in PostgreSQL 9.3

PostgreSQL 9.3 has two key software updates making switchover/switchback easier in High Availability configurations.

First, let’s address the software patches and their descriptions:
1. First patch was committed by Fujii Masao.
Patch commit# 985bd7d49726c9f178558491d31a570d47340459

With this patch, the walsender process tries to send all outstanding WAL records to the standby in replication when the user shuts down the master.

This means:
a. All WAL records are synced between two servers after the clean shutdown of the master
b. After promoting the standby to new master, the user can restart the stopped master as new standby without a fresh backup from new master.

2. Second patch was committed by Heikki Linnakangas in PostgreSQL 9.3.
Patch commit# abfd192b1b5ba5216ac4b1f31dcd553106304b19

Before PostgreSQL version 9.3, streaming replication used to stop replicating if the timeline on the primary didn’t match the standby. This generally happens when the user promotes one of the standbys to become the new master. Promoting a standby always results in an increment of timeline ID and after that increment, other standbys will refuse to continue replicating.

With this patch in PostgreSQL 9.3, the standby asks the primary for any timeline history files that are missing from the standby when it connects – if the standby recovery.conf file has the following setting:
recovery_target_timeline=’latest’

The missing files are sent using a new replication command TIMELINE_HISTORY, and stored in the standby’s pg_xlog directory. Using the timeline history files, the standby can follow the latest timeline present in the primary, just as it can follow new timelines appearing in an archive WAL directory.

Because of above patches, if the user performs the following sequence of steps then switchover/switchback can be easily achieved:
To switchover from Master to Standby, use following steps:
On Master:
1. Use any one of following stop options for clean shutdown.

    pg_ctl stop --pgdata=data_dire --mode=fast
    or
    pg_ctl stop --pgdata=data_directory --mode=smart
    

2. Before promoting standby:

Make sure all WAL send by Master applied on Standby. Use following functions to verify it:
    * pg_last_xlog_receive_location()
    * pg_last_xlog_replay_location()

3. Have the proper archive location and archive_command setting accessible to old Master.

Following is a presentation I delivered at a recent BPUG Meet (Boston PostgreSQL User Group) to discuss switchover/switchback.