Tuesday, 11 September 2018

Installing & Configuring POWA (PostgreSQL Workload Analyzer) in Linux

POWA is another very good tool for PostgreSQL DBA. It helps analyzing the stats of queries using few extensions and in UI based format. Below are some important details how to install and configure POWA in linux and view the stats over web.

Some of the important points of POWA -

  • POWA current version supports PostgreSQL version 9.4 and above but if your PostgreSQL version is less than 9.4 then there is a different version which is POWA version 1.x for it. 
  • POWA Supports various extensions ( pg_stat_statement, pg_qualstat, pg_stat_kcache,hypopg).
Some of the Pre-requisite -

  • POWA must be installed on same instance where PG is installed.
  • Shared_preload_libraries needs to be modified ( Postgres service needs reboot).
  • Contrib package needs to be installed.
  • POWA user should be created which needs superuser privileges.
  • POWA Web needs to be configured to view the stats.
Links from where Extensions can be downloaded which are needed for POWA -

  • hypopg - https://github.com/HypoPG/hypopg/releases
  • POWA - https://github.com/powa-team/
  • POWA Installation - https://powa.readthedocs.io/en/latest/quickstart.html#install-powa-from-packages-on-rhel-centos
  • PG_Qualstat - https://github.com/powa-team/pg_qualstats/archive/1.0.2.tar.gz
  • PG STAT Kcache - https://github.com/powa-team/pg_stat_kcache/archive/REL2_1_0.tar.gz
Steps to Implement POWA:
  • Install all the extension which are not available. 
  • Mandatory Extensions are - "pg_stat_statements,powa,pg_stat_kcache,pg_qualstats, hypopg” in one DB and hypopg in all the DB. 
  • Download the extensions with the above links.(make || make install)
  • Make changes in postgresql.conf file for the parameter - shared_preload_libraries='pg_stat_statements,powa,pg_stat_kcache,pg_qualstats’ 
  • Next create Database POWA and create the extensions. 
  • \c to POWA
  1. CREATE EXTENSION pg_stat_statements;
  2. CREATE EXTENSION btree_gist;
  3. CREATE EXTENSION powa;
  4. CREATE EXTENSION pg_qualstats;
  5. CREATE EXTENSION pg_stat_kcache;
  6. CREATE EXTENSION hypopg; to all DB
  7. CREATE ROLE powa SUPERUSER LOGIN PASSWORD 'astrongpassword’;
  • Install powa-web . 
  • Dependency to install powa web are - python, python-psycopg2, python-sqlalchemy & python-tornado.
  • Copy Powa Web config file to /etc/powa.conf
  • Edit powa web config file with address and port of web.
  • Start powa web
  • Check the console from web.  
Install Powa -Web:
  • wget https://pypi.io/packages/source/p/powa-web/powa-web-3.1.3.tar.gz
  • tar -zxvf powa-web-3.1.3.tar.gz
  • cd powa-web-3.1.3
  • cp ./powa-web.conf-dist ./powa-web.conf
  • ./powa-web
  • Copy powa-web.conf to /etc/ and start powa web from root user. You should be able to view the web version here - http://172.18.1.9:8888/login
Output of Powa-Web config file:
us02tespd02 ~ # cat /etc/powa-web.conf
servers={
  'us02tespd02': {
    'host': 'localhost',
    'port': '5432',
    'database': 'powa'
  },
'us02tespd03': {
    'host': '172.18.1.8',
    'port': '5432',
    'database': 'powa'
  }
}

cookie_secret="SECRET_STRING"
address="172.18.1.9"
port=8888


Posted by at No comments:

Pg_rewind in PostgreSQL

Short Summary:

Pg_rewind is a tool for synchronizing a PostgreSQL cluster with another copy of the same cluster, after the clusters' timelines have diverged. A typical scenario is to bring an old master server back online after failover, as a standby that follows the new master. The result is equivalent to replacing the target data directory with the source one. All files are copied, including configuration files. The advantage of pg_rewind over taking a new base backup, or tools like rsync, is that pg_rewind does not require reading through all unchanged files in the cluster. That makes it a lot faster when the database is large and only a small portion of it differs between the clusters.

Official Documentation Link:

IP Used for the Steps Below:

  • IP - 198.100.1.10 (Master/Primary)
  • IP - 198.100.1.11 (Slave/Replica/Secondary)

Pre-Requisite:

  1. Streaming Replication is Enabled between Master and Replica Instance.
  2. wal_log_hints should be enabled in postgresql.conf file.
  3. User used for pg_rewind should be superuser.

Steps:

  1. Suppose there are issue with Master DB(198.100.1.10) and it got crashed for some reason. We need to failover(Promote in PostgreSQL Terms) so that application connects and things are back to normal. Therefore, below command can be used to perform failover to Replica(198.100.1.11).
    Command needs to be issued from Replica -  /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data promote
  2. Now we have the new master after promotion/failover i.e. 198.100.1.11 and we need to make sure we have its replica in place as early as possible.
  3. The traditional way to build up replication is to copy the data directory using base_backup and start the replica instance. It can be very time consuming if DB size is in TB. Therefore, to reduce the effort and time, we can use pg_rewind and it sync up the new master and makes replica in few minutes as it only copies the delta of changes.
  4. To sync up the old primary(198.100.1.10) with new primary(198.100.1.11), we need to stop the old primary (198.100.1.10) using the below command issued in old primary.
    Command -  /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data stop
  5. Before we run pg_rewind, it is good to backup the below configuration files -
    A. pg_hba.conf
    B. postgresql.conf
    C. postgresql.auto.conf
    D. recovery.conf
  6. Now we are good to run pg_rewind in the old primary(To be new replica) using the below command -
    Template Command -  /usr/pgsql-9.6/bin/pg_rewind --target-pgdata /var/lib/pgsql/9.6/data --source-server="port=<Source DB Port> host=<Source Host> dbname=postgres user=<Source DB Superuse> password=<Password for Superuser>" --progress
    Example Command -  /usr/pgsql-9.6/bin/pg_rewind --target-pgdata /var/lib/pgsql/9.6/data --source-server="port=5432 host=198.100.1.11 dbname=postgres user=superuser password=SomeStrongPassword" --progress
  7. Sample output after running pg_rewind -
    connected to server
    servers diverged at WAL position 198/C3C13ED8 on timeline 1
    rewinding from last common checkpoint at 198/C3C13E30 on timeline 1
    reading source file list
    reading target file list
    reading WAL in target
    need to copy 629 MB (total source directory size is 51531 MB)
    644481/644481 kB (100%) copied
    creating backup label and updating control file
    syncing target data directory
    Done!
  8. Once pg_rewind is complete, we need to configure the recovery.conf, pg_hba.conf & postgresql.conf file if they are different in both master and replica.
  9. Sample recovery.conf file contents -
    primary_conninfo = 'host=198.100.1.10 port=5432 user=replicator password=StrongPass'
    trigger_file = '/var/lib/pgsql/9.6/data/failover'
    standby_mode = 'on'
    recovery_target_timeline = 'latest'
  10. IP of Replication User should be present in pg_hba.conf so that there is no issue with replication after failover. It is always good to keep the all the configuration file same for both Primary and Replica.
  11. Lastly, its good to check if replication is working fine using the below two commands -
    Command 1 -
    SELECT
    CASE
    WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
    ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER
    END
    AS replication_lag;
    Command 2 -
    Select pg_last_xlog_receive_location();select pg_last_xlog_replay_location(); — This value should change constantly and should have same value.
  12. Other simple way to check if replication is working fine is by creating a new DB in master and check if it replicates in Replica( If DBA is allowed to create test DB)
Posted by at No comments:

Auto Explain in PostgreSQL

Some Key Points for Auto Explain:
  1. Auto Explain helps analyzing the queries automatically in log file based on the configuration we set up.
  2. Auto Explain can be set up in session level(preferred) and also in postgresql.conf file(which needs a reboot).
  3. There are performance impact if we set up auto explain for all the queries as it takes up additional time to explain the queries and log in log file before executing it actually. 
  4. Auto Explain supports from PG version 9.3.
  5. If we add Auto Explain Plan in postgresql.conf file, it would need a reboot of the postgres service. 
  6. Auto Explain gets logged in default log file of the postgres instance.
Steps to set up Auto Explain in session( Once we disconnect from session it wont be logging auto explain plan anymore in log file)
  1. We need to issue the below commands so that auto explain starts getting logged in log file.
  2.  Command 1 - LOAD 'auto_explain';
  3. Command 2 - SET auto_explain.log_min_duration = 5; ( It is in seconds)
  4. Command 3 - SET auto_explain.log_analyze = true; ( It logs the explain plan in more detailed way)
  5. This process does not needs any reboot or reload.
Steps to configure Auto Explain Plan in postgresql.conf file:
  1. Make the below parameter changes in postgresql.conf file.
  2. Parameter 1 - shared_preload_libraries = ‘auto_explain’;
  3. Parameter 2 - auto_explain.log_min_duration = '5s'
  4. Parameter 3 - auto_explain.log_analyze = true
  5. Restart postgres service.
Posted by at No comments:
Subscribe to: Posts (Atom)