PostgreSQL

Postgres Database Monitoring using Grafana Dashboard using template

Posted on by debasis maity

1. We need to make sure that Grafana is installed already.

2. We need following postgresql dashboard to be imported in Grafana.

https://grafana.com/dashboards/4164

3. Please download and install influxDB in postgresql server to be added in Grafana monitoring.

wget https://dl.influxdata.com/influxdb/releases/influxdb-1.7.4.x86_64.rpm

sudo yum localinstall influxdb-1.7.4.x86_64.rpm

4. Please configure and restart influxDB

Please change influxdb.conf

/etc/influxdb/influxdb.conf

We need to enable it

# Determines whether user authentication is enabled over HTTP/HTTPS.

   auth-enabled = true

Please restart influxdb

# systemctl start influxdb

# systemctl status influxdb

# systemctl enable influxdb

5. Please configure and restart Telegraf

Please create telegraf user and create the database in influxdb

#curl -XPOST “http://localhost:8086/query” –data-urlencode “q=CREATE USER telegraf WITH PASSWORD ‘metricsmetricsmetricsmetrics’ WITH ALL PRIVILEGES”

#influx -username ‘telegraf’ -password ‘metricsmetricsmetricsmetrics’

[root@xxx ~]#  influx -username ‘telegraf’ -password ‘metricsmetricsmetricsmetrics’

Connected to http://localhost:8086 version 1.7.4

InfluxDB shell version: 1.7.4

Enter an InfluxQL query

>create database telegraf

> SHOW DATABASES

name: databases

name

—-

_internal

Telegraf

Please install telegraf now

wget https://dl.influxdata.com/telegraf/releases/telegraf-1.9.4-1.x86_64.rpm

sudo yum localinstall telegraf-1.9.4-1.x86_64.rpm

  • Please configure telegraf.conf to enable postgresql monitoring. You need to change IP to postgres server accordingly.

/etc/telegraf/telegraf.conf

###############################################################################

#                            OUTPUT PLUGINS                                   #

###############################################################################

# Configuration for sending metrics to InfluxDB

[[outputs.influxdb]]

  ## The full HTTP or UDP URL for your InfluxDB instance.

  ##

  ## Multiple URLs can be specified for a single cluster, only ONE of the

  ## urls will be written to each interval.

  # urls = [“unix:///var/run/influxdb.sock”]

  # urls = [“udp://127.0.0.1:8089”]

   urls = [“http://10.x.x.x:8086”]

  ## The target database for metrics; will be created as needed.

   database = “telegraf”

  ## If true, no CREATE DATABASE queries will be sent.  Set to true when using

  ## Telegraf with a user without permissions to create databases or when the

  ## database already exists.

  # skip_database_creation = false

  ## Name of existing retention policy to write to.  Empty string writes to

  ## the default retention policy.  Only takes effect when using HTTP.

  # retention_policy = “”

  ## Write consistency (clusters only), can be: “any”, “one”, “quorum”, “all”.

  ## Only takes effect when using HTTP.

   write_consistency = “any”

  ## Timeout for HTTP messages.

  # timeout = “5s”

  ## HTTP Basic Auth

   username = “telegraf”

   password = “metricsmetricsmetricsmetrics”

# # Read metrics from one or many postgresql servers

 [[inputs.postgresql]]

#   ## specify address via a url matching:

#   ##   postgres://[pqgotest[:password]]@localhost[/dbname]\

#   ##       ?sslmode=[disable|verify-ca|verify-full]

#   ## or a simple string:

#   ##   host=localhost user=pqotest password=… sslmode=… dbname=app_production

#   ##

#   ## All connection parameters are optional.

#   ##

#   ## Without the dbname parameter, the driver will default to a database

#   ## with the same name as the user. This dbname is just for instantiating a

#   ## connection with the server and doesn’t restrict the databases we are trying

#   ## to grab metrics for.

#   ##

address = “host=10.x.x.x user=pg password=pgdbpass dbname=postgres port=1523 sslmode=disable”

  • Please restart telegraf now

#telegraf

6. Now we need to login to Grafana and create 3 data sources.

For influxDB following should be data source configuration

  • PostgreSQL Master node configuration
  • PostgreSQL Slave node configuration
  • Please import the dashboard now

PostgreSQL DBA part 7 :-PostgreSQL full and incremental backup restore using BART 2.0 tool

Posted on by debasis maity

BART is a backup and recovery tool for EDB Postgres Databases

EDB Backup and Recovery Tool (BART) is a key component of an enterprise-level Postgres-based data management strategy.  BART implements retention policies and point-in-time recovery requirements for large-scale Postgres deployments. Now available, Bart 2.0 provides block-level incremental backup.

P.C https://www.enterprisedb.com/products/edb-postgres-platform/edb-backup-and-recovery-tool

A.Full backup and restore using BART 2.0 for EDB

 

1. Prerequisite: This step is required for YUM configuration to install BART tool from internet

First enable internet on server via PROXY setting as informed by Sysadmin:
Go to the /etc/profile, uncoment last lines 

proxc="http://172.16.0.65:3128/"
HTTP_PROXY=$proxc
HTTPS_PROXY=$proxc
FTP_PROXY=$proxc
http_proxy=$proxc
https_proxy=$proxc
ftp_proxy=$proxc
export HTTP_PROXY HTTPS_PROXY FTP_PROXY http_proxy https_proxy ftp_proxy
Go to the /etc/wgetrc, uncoment

http_proxy = http://172.16.0.65:3128/
https_proxy = http:// 172.16.0.65:3128/
ftp_proxy = http:// 172.16.0.65:3128/
Go to the /etc/yum.conf, uncoment

proxy=http://172.16.0.65:3128

2. Configure yum repository for BART tool

2.1 Configure your /etc/yum.repos.d with the edb repository information
rpm -Uvh http://yum.enterprisedb.com/edbrepos/edb-repo-9.6-4.noarch.rpm
(** Download the mentioned RPM)
2.2 This RPM will created the edb.repo file in the /etc/yum.repo.d/ directory path

2.3 Edit the /etc/yum.repos.d/edb.repo  file to enable (change enabled=0 to enabled=1)
 the repositories you want to use ([edbas96, enterprisedb-tools, enterprisedb-dependencies]) and specify your username / password in the connection string: (sample edb.repo file present in last point)

2.4 We need some extra package for BART installation for which we need to configure below yum  configuration repository

[root@rac1 yum.repos.d]# cat epel.repo
[epel]
name=Extra Packages for Enterprise Linux 6 - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch
failovermethod=priority
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

[epel-debuginfo]
name=Extra Packages for Enterprise Linux 6 - $basearch - Debug
#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch/debug
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-debug-6&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
gpgcheck=1

[epel-source]
name=Extra Packages for Enterprise Linux 6 - $basearch - Source
#baseurl=http://download.fedoraproject.org/pub/epel/6/SRPMS
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-source-6&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
gpgcheck=1

[root@rac1 yum.repos.d]# cat epel-testing.repo
[epel-testing]
name=Extra Packages for Enterprise Linux 6 - Testing - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/testing/6/$basearch
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=testing-epel6&arch=$basearch
failovermethod=priority
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

[epel-testing-debuginfo]
name=Extra Packages for Enterprise Linux 6 - Testing - $basearch - Debug
#baseurl=http://download.fedoraproject.org/pub/epel/testing/6/$basearch/debug
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=testing-debug-epel6&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
gpgcheck=1

[epel-testing-source]
name=Extra Packages for Enterprise Linux 6 - Testing - $basearch - Source
#baseurl=http://download.fedoraproject.org/pub/epel/testing/6/SRPMS
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=testing-source-epel6&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
gpgcheck=1

3. Run the following yum install commands:

yum install epel-release*
yum install edb-as96-server-client (if installing on a host without the psql client)
yum install edb-bart20

4. Please set the profile now

 

Login to enterprisedb user from root and set the profile

-bash-4.1$ vi .profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs
export PATH=/usr/edb/bart2.0/bin/:$PATH
PATH=$PATH:$HOME/bin

export PATH
alias config='vi /usr/edb/bart2.0/etc/bart.cfg'

. pgplus_env.sh

5. Please create directory where backup needs to be taken

[root@rac1 bin]# mkdir -p /u01/edb_bkp
[root@rac1 bin]# chmod -R 777 /u01/edb_bkp
[root@rac1 bin]# chown enterprisedb:enterprisedb /u01/edb_bkp

6.Edit the BART configuration file

 

[BART]
bart_host= enterprisedb@127.0.0.1
backup_path = /u01/edb_bkp
pg_basebackup_path = /u01/edb/as9.6/bin/pg_basebackup
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log
xlog_method = stream
retention_policy = 1 BACKUPS

[PPAS96]
host = 127.0.0.1
port = 5444
user = enterprisedb
cluster_owner = enterprisedb
description = "PPAS 96 server"
backup_name = edb_%year-%month-%dayT%hour:%minute
archive_command='cp %p %a/%f'
#allow_incremental_backups=enabled

7.Enable ssh to self user

-bash-4.1$ chown enterprisedb .ssh
-bash-4.1$ chgrp enterprisedb .ssh
-bash-4.1$ chmod 700 .ssh
-bash-4.1$
-bash-4.1$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/u01/edb/as9.6/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /u01/edb/as9.6/.ssh/id_rsa.
Your public key has been saved in /u01/edb/as9.6/.ssh/id_rsa.pub.
The key fingerprint is:
f7:72:e1:17:c3:5d:c9:3a:f8:e9:eb:9c:6e:e0:53:be enterprisedb@edb2prod
The key's randomart image is:
+--[ RSA 2048]----+
| |
| . .|
| o.|
| .....|
| S ...o+ .|
| . +.ooo |
| o Bo. |
| =o+. |
| =Eo |
+-----------------+
-bash-4.1$ pwd
/u01/edb/as9.6
-bash-4.1$ cd .ssh/
-bash-4.1$ ll
total 12
-rw-------. 1 enterprisedb enterprisedb 1675 Nov 7 21:13 id_rsa
-rw-r--r--. 1 enterprisedb enterprisedb 403 Nov 7 21:13 id_rsa.pub
-rw-r--r--. 1 enterprisedb enterprisedb 1968 Nov 3 21:47 known_hosts
-bash-4.1$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
-bash-4.1$ chmod 600 ~/.ssh/authorized_keys
-bash-4.1$ ssh enterprisedb@127.0.0.1
The authenticity of host '127.0.0.1 (127.0.0.1)' can't be established.
RSA key fingerprint is b7:b2:44:3f:5b:19:8a:7d:13:a3:a9:45:67:45:7d:8c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '127.0.0.1' (RSA) to the list of known hosts.
Last login: Thu Apr 20 10:33:36 2017 from 172.19.8.95

8.Add the appropriate entries on pg_hba.conf file:

local all enterprisedb trust
host replication enterprisedb 127.0.0.1/32 trust
host template1 enterprisedb 127.0.0.1/32 trust

9.Now we can check whether backup configuration is fine or not

-bash-4.1$ bart check-config
INFO: Verifying that pg_basebackup is executable
INFO: success - pg_basebackup(/u01/edb/as9.6/bin/pg_basebackup) returns version 9.600000

-bash-4.1$ bart check-config -s ppas96
INFO: Checking server ppas96
INFO: Verifying cluster_owner and ssh/scp connectivity
INFO: success
INFO: Verifying user, host, and replication connectivity
INFO: success
INFO: Verifying that user is a database superuser
INFO: success
INFO: Verifying that cluster_owner can read cluster data files
INFO: success
INFO: Verifying that you have permission to write to vault
INFO: success
INFO: /u01/edb_bkp/ppas96
INFO: Verifying database server configuration
INFO: success
INFO: Verifying that WAL archiving is working
INFO: waiting 30 seconds for /u01/edb_bkp/ppas96/archived_wals/000000020000000000000027
INFO: waiting 20 seconds for /u01/edb_bkp/ppas96/archived_wals/000000020000000000000027
INFO: waiting 10 seconds for /u01/edb_bkp/ppas96/archived_wals/000000020000000000000027
ERROR: can't verify archiving: required file has not reached archived_wals directory

10.Now we can initialize the catalog

-bash-4.1$ bart INIT -s PPAS96 -o
INFO: setting archive_command for server 'ppas96'
WARNING: archive_command is set. server restart is required

11.Take full backup now

-bash-4.1$ bart backup -s ppas96 -F p
INFO: creating backup for server 'ppas96'
INFO: backup identifier: '1527547489386'
106132/106132 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1527547489386
BACKUP NAME: edb_2018-05-28T18:44
BACKUP PARENT: none
BACKUP LOCATION: /u01/edb_bkp/ppas96/1527547489386
BACKUP SIZE: 118.45 MB
BACKUP FORMAT: plain
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: stream
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 0
START WAL LOCATION: 000000020000000000000029
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2018-05-28 18:44:50 EDT
STOP TIME: 2018-05-28 18:44:53 EDT
TOTAL DURATION: 3 sec(s)

12.Verify the backup now

-bash-4.1$ bart show-backups
SERVER NAME BACKUP ID BACKUP NAME BACKUP PARENT BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS

ppas96 1527547489386 edb_2018-05-28T18:44 none 2018-05-28 18:44:53 EDT 118.45 MB 0.00 bytes 0 active

13.Create a demo database (tst) and create some sample tables.We will verify if we will be able to restore this database and table after we fully restore backup

psql
edb=# create database tst;
CREATE DATABASE

sales=# \c tst
You are now connected to database "tst" as user "enterprisedb".
tst=# create table article(id int);
CREATE TABLE
tst=# insert into article values (1);
INSERT 0 1
tst=# table article;
id
----
1
(1 row)

14.Create the full backup again

-bash-4.1$ bart backup -s ppas96 -F p
INFO: creating backup for server 'ppas96'
INFO: backup identifier: '1527547795678'
117720/117720 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1527547795678
BACKUP NAME: edb_2018-05-28T18:49
BACKUP PARENT: none
BACKUP LOCATION: /u01/edb_bkp/ppas96/1527547795678
BACKUP SIZE: 129.58 MB
BACKUP FORMAT: plain
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: stream
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 0
START WAL LOCATION: 00000002000000000000002B
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2018-05-28 18:49:59 EDT
STOP TIME: 2018-05-28 18:50:00 EDT
TOTAL DURATION: 1 sec(s)

15.Simulate the crash now after moving data directory to another name and create empty directory

-bash-4.1$ cd /u01/edb/as9.6/
-bash-4.1$ mv data data_old
-bash-4.1$ mkdir -p /u01/edb/as9.6/data

16.Please restore full backup now

-bash-4.1$ bart RESTORE -s PPAS96 -i 1527547795678 -p /u01/edb/as9.6/data/
INFO: restoring backup '1527547795678' of server 'ppas96'
INFO: base backup restored
INFO: archiving is disabled
INFO: permissions set on $PGDATA
INFO: restore completed successfully

17.Before starting cluster, let’s create a recovery.conf file

open /u01/edb/as9.6/recovery.conf and add the following lines

restore_command='cp /u01/edb/as9.6/data/archived_wals/%f %p'

18.Please restart database

-bash-4.1$ pg_ctl -D /u01/edb/as9.6/data -mf start
server starting
-bash-4.1$ 2018-05-29 19:02:37 EDT LOG: redirecting log output to logging collector process
2018-05-29 19:02:37 EDT HINT: Future log output will appear in directory "pg_log".

19.After the restore finished, the recovery.conf is automatically renamed to recovery.done because PostgreSQL will go into recovery mode again after a restart otherwise.

-bash-4.1$ cat recovery.done
restore_command=’cp /u01/edb/as9.6/data/archived_wals/%f %p’

20.Now we will check if the database and table restored successfully after restore full database

-bash-4.1$ psql
psql.bin (9.6.2.7)
Type "help" for help.

edb=# \c tst
You are now connected to database "tst" as user "enterprisedb".
tst=# table article;
id
----
1
(1 row)

B.Incremental backup and restore using BART 2.0 for EDB

 

1.Please enable allow_incremental_backup in BART configuration file

Set the parameter allow_incremental_backups to ENABLED in the BART configuration file.Restart the server and post that check whether the INCREMENTAL BACKUP feature has been enabled or not.

2.With BART 2.0 there is a new component called the “WAL scanner”. The binary is at the same location as BART itself

3.Start WAL_SCANNER

The WAL scanner (as the name implies) scans the WALs that are archived from your PostgreSQL instances to the BART host for changes and then writes a so called “modified block map (MBM)” (this is the reason why wal compression is not supported in incremental backup). As this should happen as soon as the WALs arrive on the BART host the WAL scanner should run all the time.
We need to start it in daemon mode :

-bash-4.1$ /usr/edb/bart2.0/bin/bart-scanner --daemon
-bash-4.1$ ps -ef|grep bart
54323 27284 1 0 20:30 ? 00:00:00 /usr/edb/bart2.0/bin/bart-scanner --daemon
54323 27285 27284 0 20:30 ? 00:00:00 /usr/edb/bart2.0/bin/bart-scanner --daemon
54323 27318 4555 0 20:30 pts/0 00:00:00 grep bart

4.Re-check configuration settings:

-bash-4.1$ bart check-config -s PPAS96
INFO: Checking server PPAS96
INFO: Verifying cluster_owner and ssh/scp connectivity
INFO: success
INFO: Verifying user, host, and replication connectivity
INFO: success
INFO: Verifying that user is a database superuser
INFO: success
INFO: Verifying that cluster_owner can read cluster data files
INFO: success
INFO: Verifying that you have permission to write to vault
INFO: success
INFO: /u01/edb_bkp/ppas96
INFO: Verifying database server configuration
INFO: success
INFO: Verifying that WAL archiving is working
INFO: waiting 30 seconds for /u01/edb_bkp/ppas96/archived_wals/00000003000000000000002C
INFO: success
INFO: Verifying that bart-scanner is configured and running
INFO: success

5.Execute a DB FULL backup post enabling WAL_SCANNER:

-bash-4.1$ bart backup -s PPAS96 --backup-name full0
INFO: creating backup for server 'ppas96'
INFO: backup identifier: '1527640567564'
pg_basebackup: WAL streaming can only be used in plain mode
Try "pg_basebackup --help" for more information.

ERROR: backup failed for server 'ppas96'
command failed with exit code 1
Try "pg_basebackup --help" for more information.

Changed the xlog_method = fetch in bart config file and  execute following command

-bash-4.1$ bart INIT -s PPAS96 -o
INFO: setting archive_command for server 'ppas96'
WARNING: archive_command is set. server restart is required
-bash-4.1$ bart backup -s PPAS96 --backup-name full0
INFO: creating backup for server 'ppas96'
INFO: backup identifier: '1527640746966'
134117/134117 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO: backup checksum: 7de18e9077a038e2944ee4b0f92e2612 of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1527640746966
BACKUP NAME: full0
BACKUP PARENT: none
BACKUP LOCATION: /u01/edb_bkp/ppas96/1527640746966
BACKUP SIZE: 130.97 MB
BACKUP FORMAT: tar
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
7de18e9077a038e2944ee4b0f92e2612 base.tar

TABLESPACE(s): 0
START WAL LOCATION: 00000003000000000000002F
STOP WAL LOCATION: 00000003000000000000002F
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2018-05-29 20:39:08 EDT
STOP TIME: 2018-05-29 20:39:08 EDT
TOTAL DURATION: 0 sec(s)
-bash-4.1$ bart show-backups
SERVER NAME BACKUP ID BACKUP NAME BACKUP PARENT BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS

ppas96 1527640746966 full0 none 2018-05-29 20:39:09 EDT 130.98 MB 16.00 MB 1 active
ppas96 1527547795678 edb_2018-05-28T18:49 none 2018-05-28 18:50:00 EDT 129.58 MB 48.00 MB 3 active
ppas96 1527547489386 edb_2018-05-28T18:44 none 2018-05-28 18:44:53 EDT 118.45 MB 0.00 bytes 0 active

6.Now create a demo table and insert some rows

-bash-4.1$ psql
psql.bin (9.6.2.7)
Type "help" for help.

edb=# \c tst
You are now connected to database "tst" as user "enterprisedb".
tst=# create table chk_incr(c1 char(10));
CREATE TABLE
tst=# insert into chk_incr values('DEBASIS');
INSERT 0 1
tst=# table chk_incr;
c1
------------
DEBASIS
(1 row)

7.Please take incremental backup now with tag incr1

-bash-4.1$ bart backup -s ppas96 -F p --parent 1527640746966 --backup-name incr1
INFO: creating incremental backup for server 'ppas96'
INFO: checking mbm files /u01/edb_bkp/ppas96/archived_wals
INFO: new backup identifier generated 1527641049438
INFO: reading directory /u01/edb_bkp/ppas96/archived_wals
INFO: all files processed
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
INFO: incremental backup completed successfully
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1527641049438
BACKUP NAME: incr1
BACKUP PARENT: 1527640746966
BACKUP LOCATION: /u01/edb_bkp/ppas96/1527641049438
BACKUP SIZE: 33.34 MB
BACKUP FORMAT: plain
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 0
START WAL LOCATION: 000000030000000000000031
STOP WAL LOCATION: 000000030000000000000032
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2018-05-29 20:44:10 EDT
STOP TIME: 2018-05-29 20:44:12 EDT
TOTAL DURATION: 2 sec(s)

8.Please insert another values in the table.

-bash-4.1$ psql
psql.bin (9.6.2.7)
Type "help" for help.

edb=# \c tst
You are now connected to database "tst" as user "enterprisedb".
tst=# insert into chk_incr values('MAITY');
INSERT 0 1
tst=# table chk_incr;
c1
------------
DEBASIS
MAITY
(2 rows)

tst=# quit

9.Please take another incremental backup tag as incr2

-bash-4.1$ bart backup -s ppas96 -F p --parent 1527640746966 --backup-name incr2
INFO: creating incremental backup for server 'ppas96'
INFO: checking mbm files /u01/edb_bkp/ppas96/archived_wals
INFO: new backup identifier generated 1527641642651
INFO: reading directory /u01/edb_bkp/ppas96/archived_wals
INFO: all files processed
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
INFO: incremental backup completed successfully
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1527641642651
BACKUP NAME: incr2
BACKUP PARENT: 1527640746966
BACKUP LOCATION: /u01/edb_bkp/ppas96/1527641642651
BACKUP SIZE: 33.34 MB
BACKUP FORMAT: plain
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 0
START WAL LOCATION: 000000030000000000000034
STOP WAL LOCATION: 000000030000000000000035
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2018-05-29 20:54:03 EDT
STOP TIME: 2018-05-29 20:54:04 EDT
TOTAL DURATION: 1 sec(s)

10.Please check all backups available now

-bash-4.1$ bart show-backups
SERVER NAME BACKUP ID BACKUP NAME BACKUP PARENT BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS

ppas96 1527641642651 incr2 full0 2018-05-29 20:54:04 EDT 33.34 MB active
ppas96 1527641049438 incr1 full0 2018-05-29 20:44:11 EDT 33.34 MB active
ppas96 1527640746966 full0 none 2018-05-29 20:39:09 EDT 130.98 MB 112.00 MB 7 active
ppas96 1527547795678 edb_2018-05-28T18:49 none 2018-05-28 18:50:00 EDT 129.58 MB 48.00 MB 3 active
ppas96 1527547489386 edb_2018-05-28T18:44 none 2018-05-28 18:44:53 EDT 118.45 MB 0.00 bytes 0 active

11.Now I will restore incr1 tagged backup.Please create empty directory /u01/edb_incr1 owner as enterprisedb.

-bash-4.1$ bart restore -s ppas96 -i 1527641049438 -p /u01/edb_incr1/
INFO: restoring incremental backup '1527641049438' of server 'ppas96'
INFO: base backup restored
INFO: archiving is disabled
INFO: permissions set on $PGDATA
INFO: incremental restore completed successfully

12.Please stop old instance and restart instance with new path where we restored incremental backup.

-bash-4.1$ pg_ctl -D /u01/edb/as96/data -mf stop
waiting for server to shut down.... done
server stopped
-bash-4.1$ pg_ctl -D /u01/edb_incr1 -mf start
server starting
-bash-4.1$ 2018-05-30 02:54:34 EDT LOG: redirecting log output to logging collector process
2018-05-30 02:54:34 EDT HINT: Future log output will appear in directory "pg_log".

13.Now we can check we successfully restored incr1 backup verifying table data.

-bash-4.1$ psql
psql.bin (9.6.2.7)
Type "help" for help.

edb=# \c tst
You are now connected to database "tst" as user "enterprisedb".
tst=# table chk_incr;
c1
------------
DEBASIS
(1 row)

PostgreSQL DBA part 6 :-PostgreSQL high availability setup using EFM

Posted on by debasis maity

PostgreSQL high availability setup using EFM

 

A.Introduction

This document provides you the overview of High Availability Setup for EDB Postgres 9.5 database.

An EDB Postgres Failover Manager (EFM) cluster is comprised of Failover Manager Processes that reside on the following hosts on a network:

  1. A Master node – The Master node is the primary database server that is servicing database clients.
  2. One or more Standby nodes – A Standby node is a streaming replication server associated with the Master node.
  3. Witness Node (optional)- The Witness node confirms assertions of either the Master or a Standby in a fail over scenario. A cluster does not need a dedicated witness node if the cluster contains three or more nodes; if you do not have a third cluster member that is a database host, you can add a dedicated Witness node.

 

B.Architecture(For best practice)

The Architecture for this environment shown in the below picture. It has 2 application nodes (VM1 &VM2) in primary site which are connected to VIP of EDB .  Application nodes are running on JBOSS 6.4 application server and both of the servers are running in standalone mode, pointing to the same metadata deployed at the same time (Which is the VIP : 172.XX.XX.5:5444/edb).

For EDB it has two nodes (VM3 and VM4) as active and standby mode , which is pointed by VIP. VM3 and VM4 are connected in Sync mode. For DR site , two DB nodes are present which are VM5 and VM6 . Both VM5 and VM6 are connected in ASYNC mode with main DB node on primary site.

The below configuration is made on EDB Test instance and will consist of 1 Master server and 1 standby server. The standby will be in sync with the primary to ensure no data loss happens during failover.

But for our test case,We have simplified installation where We have one site in primary and read replica or standby site in secondary.The whole test was done in AWS.

Below are the host details where the installation will be made:

NODE IP ROLE
edb1tst 172.31.20.93 Primary
edb2tst 172.31.20.82 Standby

C.Install PostgreSQL in both primary and standby nodes using below :-

https://clouddba.co/postgresql-dba-part1-installation-of-postgresql-9-6-in-linux/

D.Install EFM 2.1 RPM which is package provided by EDB for HA setup

[root@edb1tst home]# rpm -ivh ./edb_tools_redhat_rhel-6-x86_64/efm21-2.1.1-1.rhel6.x86_64.rpm

warning: ./edb_tools_redhat_rhel-6-x86_64/efm21-2.1.1-1.rhel6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 7e30651c: NOKEY

Preparing...                ########################################### [100%]

1:efm21                  ########################################### [100%]

E.Modify the /opt/edb/as9.6/data/pg_hba.conf  to include all the entries of Primary and standby nodes:

For Primary

# "local" is for Unix domain socket connections only
host all all 0.0.0.0/0 trust
local all all trust
# IPv4 local connections:
host edb all 172.31.20.93/32 trust
# IPv6 local connections:
##host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication enterprisedb trust
host prod all 172.31.20.93/32 trust
host prod all 172.31.20.82/32 trust
host replication enterprisedb 172.31.20.93/32 trust
host replication enterprisedb 172.31.20.82/32 trust

For Standby

# "local" is for Unix domain socket connections only
host all all 0.0.0.0/0 trust
local all all trust
# IPv4 local connections:
host edb all 172.31.20.82/32 trust
# IPv6 local connections:
##host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication enterprisedb trust
host prod all 172.31.20.93/32 trust
host prod all 172.31.20.82/32 trust
host replication enterprisedb 172.31.20.93/32 trust
host replication enterprisedb 172.31.20.82/32 trust

Please restart DB

pg_ctl -D /opt/edb/as9.6/data stop
pg_ctl -D /opt/edb/as9.6/data start
pg_ctl -D /opt/edb/as9.6/data status

F.Check and turn off the FIREWALL on the servers (both Primary and Standby)

 

G.Encrypt efm password on both nodes:For my case username is enterprisedb and password is enterprisedb

/usr/efm-2.1/bin/efm encrypt efm
This utility will generate an encrypted password for you to place in your
EFM cluster property file.
Please enter the password and hit enter:
Please enter the password again to confirm:
The encrypted password is: 2ae8d8d6fbfb742863990dc444e9434f
Please paste this into your cluster properties file.
        db.password.encrypted=2ae8d8d6fbfb742863990dc444e9434f

Troubleshoot if in case it fails:-

### Error encoutered

[root@edb2prod home]# efm encrypt efm
This utility will generate an encrypted password for you to place in your
EFM cluster property file.
Please enter the password and hit enter:
Please enter the password again to confirm:
Could not encode password: Illegal key size or default parameters
If full-strength encryption has not been installed in the current version of Java, search for “<vendor name> java full strength encryption” for more information.
### Solution
For this download “Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files 8” for JDK 1.8 from below link and copy that to /usr/java/jdk1.8.0_121/jre/lib/security location:
http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html
[root@edb2test security]# ll *jar*
-rw-r--r--. 1 root root 3035 Dec 20  2013 local_policy.jar
-rw-r--r--. 1 root root 3405 Apr 11 15:47 local_policy.jar_bkp
-rw-r--r--. 1 root root 3023 Dec 20  2013 US_export_policy.jar
-rw-r--r--. 1 root root 2920 Apr 11 15:47 US_export_policy.jar_bkp

[root@edb2test security]# efm encrypt efm
This utility will generate an encrypted password for you to place in your
EFM cluster property file.
Please enter the password and hit enter:
Please enter the password again to confirm:
The encrypted password is: 2ae8d8d6fbfb742863990dc444e9434f
Please paste this into your cluster properties file.
        db.password.encrypted=2ae8d8d6fbfb742863990dc444e9434f
H.STEP-BY-STEP IMPLEMENTATION PROCEDURE

Steps in PRIMARY server:

1. Add the below contents to pgpass

su – enterprisedb -c “echo \”*:*:*:enterprisedb:enterprisedb\” > ~/.pgpass”
su – enterprisedb -c “chmod 600 ~/.pgpass”

2.Set permissions and ownerships

 -bash-4.1$ chown enterprisedb:enterprisedb data

-bash-4.1$ chmod -R 700 data

3.Check the status of pg_ctl process as below:

 

-bash-4.1$ /opt/edb/as9.6/bin/pg_ctl -D /opt/edb/as9.6/data status

pg_ctl: server is running (PID: 683)

/opt/edb/as9.6/bin/edb-postgres "-D" "/opt/edb/as9.6/data"

NOTE: Here in this installation we already had a single node instance running on the system, and application connectivity was established. Since we’re only changing the single installation to HA hence we just checked the above status.

If this is a fresh installation then execute the below command:

 [root@rac4 as9.6]# su - enterprisedb -c "/opt/edb/as9.6/bin/initdb -D /opt/edb/as9.6/data"
 And then check the status:

/opt/edb/as9.6/bin/pg_ctl -D /opt/edb/as9.6/data start

4. Modify the efm.nodes to include the standby server entries:

 5.Modify the bash profile as below and run encrypt the EFM password as below:

6.Export the below variables on the terminal as root user and set the password for enterprisedb user

 

export YUM_USER=XXXXX
export YUM_PASSWORD=test#123
export MASTER_IP=172.31.20.93
export STDBY_IP=172.31.20.82
export YOUR_EMAIL=XX.XX@gmail.com
export PWD_ENCRYPT=2ae8d8d6fbfb742863990dc444e9434f
export PGDATA=/opt/edb/as9.6/data

 

su - enterprisedb -c "/opt/edb/as9.6/bin/psql -p 5444 -d edb -c \"alter user enterprisedb identified by enterprisedb\""

 7.Add the below entries at the end of conf file

vi /opt/edb/as9.6/data/postgresql.conf

 

wal_level = hot_standby
max_wal_senders = 3
hot_standby = on
archive_mode = on
archive_command = 'cd .'
logging_collector = on    (Check is it already on or not)

8.Restart the database again

pg_ctl -D /opt/edb/as9.6/data stop
pg_ctl -D /opt/edb/as9.6/data start
pg_ctl -D /opt/edb/as9.6/data status

9.Add the following entries in efm.properties file:

 

sed -i "s/db.user=/db.user=enterprisedb/" /etc/efm-2.1/efm.properties
sed -i "s/db.password.encrypted=/db.password.encrypted=$PWD_ENCRYPT/" /etc/efm-2.1/efm.properties
sed -i "s/db.port=/db.port=5444/" /etc/efm-2.1/efm.properties
sed -i "s/db.database=/db.database=edb/" /etc/efm-2.1/efm.properties
sed -i "s/admin.port=/admin.port=7809/" /etc/efm-2.1/efm.properties
sed -i "s/user.email=/user.email=$YOUR_EMAIL/" /etc/efm-2.1/efm.properties
sed -i "s/bind.address=/bind.address=$MASTER_IP:7800/" /etc/efm-2.1/efm.properties
sed -i "s/is.witness=/is.witness=false/" /etc/efm-2.1/efm.properties
sed -i "s/db.service.owner=/db.service.owner=enterprisedb/" /etc/efm-2.1/efm.properties
sed -i "s;db.recovery.conf.dir=;db.recovery.conf.dir=/opt/edb/as9.6/data;g" /etc/efm-2.1/efm.properties
sed -i "s/db.bin=/db.bin=\/opt\/edb\/as9.6\/bin/" /etc/efm-2.1/efm.properties
sed -i "s/auto.allow.hosts=false/auto.allow.hosts=true/" /etc/efm-2.1/efm.properties

 10.Execute the below command to check whether we’re getting the edb prompt:

 

NOTE: We received the above highlighted error during starting the services. For this edit the pingServerIp property in efm.properties file to the local hostname: pingServerIp=172.19.18.95 (this is because the server is not having internet connectivity presently)

11.Restart the pg_ctl services

pg_ctl -D /opt/edb/as9.6/data stop
pg_ctl -D /opt/edb/as9.6/data start
pg_ctl -D /opt/edb/as9.6/data status

NOTE: We have got errors in the mentioned file:

Resolution:

vi “/opt/edb/as9.6/data/postgresql.conf”

logging_collector = on    #(Check is it already on or not) (**comment it as highlighted)

Steps in STANDBY server:

 

1.Export the variables below in terminal as root user:

export YUM_USER=XXXXX
export YUM_PASSWORD=test#123
export MASTER_IP=172.31.20.93
export STDBY_IP=172.31.20.82
export YOUR_EMAIL=XX.XX@gmail.com
export PWD_ENCRYPT=2ae8d8d6fbfb742863990dc444e9434f
export PGDATA=/opt/edb/as9.6/data

 2.Modify the ownership and permission same as primary

 su - enterprisedb -c "echo \"*:*:*:enterprisedb:enterprisedb\" > ~/.pgpass"
 su - enterprisedb -c "chmod 600 ~/.pgpass"

 3.Take backup of data directory if present earlier:

Then execute the below commands:

[root@edb2tst as9.6]# mv data_bkp data

[root@edb2tst as9.6]# mkdir data

[root@edb2tst as9.6]# chown enterprisedb:enterprisedb data

[root@edb2tst as9.6]# chmod 0700 data

[root@edb2tst as9.6]# su - enterprisedb -c "/opt/edb/as9.6/bin/pg_basebackup -h 172.31.20.93 -p 5444 -R -D /opt/edb/as9.6/data"

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

4.Start Server

[root@edb2tst data]# su – enterprisedb -c ” /opt/edb/as9.6/bin/pg_ctl -w -D /opt/edb/as9.6/data start”

5.Add the following entries in conf file:

[root@edb2tst data]# echo trigger_file=\'/opt/edb/as9.6/trigger.file\' >> /opt/edb/as9.6/recovery.conf

[root@edb2tst data]# echo recovery_target_timeline='latest' >>/opt/edb/as9.6/recovery.conf

 6.copy efm.proporties file from node 1(primary) to standby

 7.Edit the bind.address to the below entry on standby

bind.address=172.31.20.82:7800

Add the below entry to efm.nodes file

172.31.20.93:7800

I.Final steps to start EFM and check .These steps needs to be done both nodes

 

1.Edit both the efm.nodes and put the IP of all nodes in the cluster:

In primary

vi /etc/efm-2.1/efm.nodes

172.31.20.93:7800
172.31.20.82:7800

 In Standby

vi /etc/efm-2.1/efm.nodes

172.31.20.93:7800
172.31.20.82:7800

2.Stop/Start both the DB and efm service

su - enterprisedb -c "/opt/edb/as9.6/bin/pg_ctl -D /opt/edb/as9.6/data  stop"

su - enterprisedb -c "/opt/edb/as9.6/bin/pg_ctl -D /opt/edb/as9.6/data  stop"

3.Stop and start EFM services

service efm-2.1 stop

service efm-2.1 start
4.Now you can check cluster status
[root@ip-172-31-20-93 ~]# /usr/efm-2.1/bin/efm cluster-status efm
Cluster Status: efm
VIP:

Agent Type Address Agent DB Info
--------------------------------------------------------------
Standby 172.31.20.82 UP UP
Master 172.31.20.93 UP UP

Allowed node host list:
172.31.20.93 172.31.20.82

Membership coordinator: 172.31.20.82

Standby priority host list:
172.31.20.82

Promote Status:

DB Type Address XLog Loc Info
--------------------------------------------------------------
Master 172.31.20.93 0/700DDE0
Standby 172.31.20.82 0/700DDE0

Standby database(s) in sync with master. It is safe to promote.

5.How to establish SYNC 

For establishing SYNCHRONOUS (ie SYNC) connectivity :

Modify the below parameter in the below file:

In primary:

-bash-4.1$ vi postgresql.conf

-bash-4.1$ pwd


/opt/edb/as9.6/data 

 

In standby:

-bash-4.1$ vi recovery.conf

standby_mode = 'on'

primary_conninfo = 'user=enterprisedb password=enterprisedb host=172.19.18.95 port=5444 sslmode=prefer sslcompression=1 krbsrvname=postgres application_name = eocapp'

trigger_file='/opt/edb/as9.6/data/trigger.file' recovery_target_timeline=latest

 

PostgreSQL DBA part 5 :- Create user and grant privilege in postgresql

Posted on by debasis maity 
edb=# create user test identified by test;
CREATE ROLE
edb=# grant connect on database edb to test;
GRANT
edb=# grant usage on schema public to test;
GRANT
edb=# create table t(c1 char(10));
CREATE TABLE
edb=# insert into t values('DEB');
INSERT 0 1
edb=# commit;
COMMIT
edb=# grant select,insert on emp to test;
GRANT
edb=# \c edb test;
Password for user test:
You are now connected to database "edb" as user "test".
edb=> select * from emp;
 empno | ename | job | mgr | hiredate | sal | comm | de
ptno
-------+--------+-----------+------+--------------------+---------+---------+---
-----
 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | |
 20

PostgreSQL DBA part 4 :-How to create database in postgresql

Posted on by debasis maity

How to create database in postgresql

Create Database command can be used to create a database in a cluster.
−Syntax:
Creating Databases
CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner][ TEMPLATE [=] template ][ ENCODING [=] encoding ][ TABLESPACE [=] tablespace][ CONNECTION LIMIT [=] connlimit ] ]

1.Create database in postgresql  named prod with user as enterprisedb.Please note enterprisedb is OS user as well.

edb=# create database prod owner enterprisedb;
CREATE DATABASE

2.Connect to newly created database in postgresql.

edb=# \connect prod
You are now connected to database "prod" as user "enterprisedb".

3.Please check whether you can see newly created database

prod=# select datname from pg_database;
 datname
-----------
 postgres
 edb
 template1
 template0
 prod
(5 rows)

prod=# exit

PostgreSQL Parameters(The following are most common parameters in postgresql.conf)

Posted on by debasis maity

A.Many Ways to set postgresql parameters

•Some parameters can be changed per session using the SETcommand.
•Some parameters can be changed at the user level using ALTER USER.
•Some parameters can be changed at the database level using ALTER DATABASE.
•The SHOW command can be used to see settings.
•The pg_settings and pg_file_settings catalog table lists settings information.

B.PostgreSQL Parameters(The following are most common parameters in postgresql.conf)

1.Connection Settings

 

 Parameter  Default Value  Description
listen_addresses localhost Specifies the addresses on which the server is to listen for connections. Use * for all.
port 5432 The port the server listens on.
max_connections 100 Maximum number of concurrent connections the server can support.
superuser_reserved_connections 3 Number of connection slots reserved for superusers.
unix_socket_directory /tmp Directory to be used for UNIX socket connections to the server
unix_socket_permissions 0777 access permissions of the Unix-domain socket.

2.Security and Authentication Settings

 

 Parameter  Default Value  Description
authentication_timeout 1 minute Maximum time to complete client authentication, in seconds.
ssl off Enables SSL connections.
ssl_ca_file Specifies the name of the file containing the SSL server certificate authority (CA).
ssl_cert_file Specifies the name of the file containing the SSL server certificate.
ssl_key_file Specifies the name of the file containing the SSL server private key.
ssl_ciphers List of SSL ciphers that may be used for secure connections.

3.Memory Settings

 

 Parameter  Default Value  Description
shared_buffers 128 MB Size of PostgreSQL shared buffer pool for a cluster.
temp_buffers 8 MB Amount of memory used by each backend for caching temporary table data.
work_mem 4 MB Amount of memory used for each sort or hash operation before switching to temporary disk files.
maintenance_work_mem 64 MB Amount of memory used for each index build or VACUUM.
temp_file_limit 1 amount of disk space that a session can use for temporary files. A transaction attempting to exceed this limit will be cancelled. Default is unlimited.

4.Query Planner Settings

 

 Parameter  Default Value  Description
random_page_cost 4.0 Estimated cost of a random page fetch, in abstract cost units. May need to be reduced to account for caching effects.
seq_page_cost 1.0 Estimated cost of a sequential page fetch, in abstract cost units. May need to be reduced to account for caching effects. Must always set random_page_cost >= seq_page_cost
effective_cache_size 4 GB Used to estimate the cost of an index scan. Rule of thumb is 75% of system memory.

5.Write Ahead Log Settings

 

 Parameter  Default value  Description
wal_level minimum Determines how much information is written to the WAL. Change this to enable replication. Other values are archive, logical and hot_standby.
fsync on Turn this off to make your database much faster –and silently cause arbitrary corruption in case of a system crash.
wal_buffer -1, autotune) The amount of memory used in shared memory for WAL data. The default setting of -1 selects a size equal to 1/32nd (about 3%) of shared_buffers.
min_wal_size 80 MB The WAL size to start recycling the WAL files.
max_wal_size 1 GB The WAL size to start checkpoint.Controls the number of WAL Segments(16MB each) after which checkpoint is forced.
checkpoint_timeout 5 minutes Maximum time between checkpoints.
wal_compression off The WAL of Full Page write will be compressed and written.

6.Where To Log

log_destination-Valid values are combinations of stderr, csvlog, syslog, and eventlog, depending on platform.
logging_collector-Enables advanced logging features. csvlogrequires logging_collector.
‒log_directory-Directory where log files are written. Requires logging_collector.
‒log_filename-Format of log file name (e.g. postgresql-%Y-%M-%d.log). Allows regular log rotation. Requires logging_collector.
‒log_file_mode(default 0600) -On Unix systems this parameter sets the permissions for log files when logging_collectoris enabled.
‒log_rotation_age-Automatically rotate logs after this much time. Requires logging_collector.
‒log_rotation_size-Automatically rotate logs when they get this big. Requires logging_collector.

7.When To Log

 

Parameter Default value Description
client_min_messages NOTICE Messages of this severity level or above are sent to the client
log_min_messages WARNING Messages of this severity level or above are sent to the server.
log_min_error_statement ERROR When a message of this severity or higher is written to the server log, the statement that caused it is logged along with it.
log_min_duration_statement default -1, disabled When a statement runs for at least this long, it is written to the server log, with its duration.

8.What to log

 

Parameter Default value Description
log_connections off Log successful connections to the server log.
log_disconnections off Log some information each time a session disconnects, including the duration of the session.
log_error_verbosity default Can also select “terse” or “verbose”.
log_duration off Log duration of each statement.
log_line_prefix Additional details to log with each line.
log_statement none Legal values are none, ddl, mod (DDL and all other data-modifying statements), or all.
log_temp_files -1 Log temporary files of this size or larger, in kilobytes.
log_checkpoints off Causes checkpoints and restartpoints to be logged in the server log.

9.Background Writer Settings

 

Parameter Default value Description
bgwriter_delay 200 ms Specifies time between activity rounds for the background writer.
bgwriter_lru_maxpages 100 Maximum number of pages that the background writer may clean per activity round.
bgwriter_lru_multiplier 2.0 Multiplier on buffers scanned per round. By default, if system thinks 10 pages will be needed, it cleans 10 * bgwriter_lru_multiplierof 2.0 = 20.

10.Statement Behavior

 

Parameter Default value Description
search_path This parameter specifies the order in which schemas are searched. The default value for this parameter is”$user”, public.
default_tablespace Name of the tablespace in which to objects are created by default.
temp_tablespaces Tablespaces name(s) in which temporary objects are created.
statement_timeout Postgres will abort any statement that takes over the specified number of milliseconds A value of zero (the default) turns this off.

11.Vacuum Cost Settings

 

Parameter Default value Description
vacuum_cost_delay 0 ms The length of time, in milliseconds, that the process will wait when the cost limit is exceeded.
vacuum_cost_page_hit 1 The estimated cost of vacuuming a buffer found in the PostgreSQL buffer pool.
vacuum_cost_page_miss 10 The estimated cost of vacuuming a buffer that must be read into the buffer pool.
vacuum_cost_page_dirty 20 The estimated cost charged when vacuum modifies a buffer that was previously clean.
vacuum_cost_limit 200 The accumulated cost that will cause the vacuuming process to sleep.

12.Autovacuum Settings

 

Parameter Default value Description
autovacuum on Controls whether the autovacuum launcher runs, and starts worker processes to vacuum and analyze tables.
log_autovacuum_min_duration -1 Autovacuum tasks running longer than this duration are logged. Can now be specified per table.
autovacuum_max_workers 3 Maximum number of autovacuum worker processes which may be running at one time.
autovacuum_work_mem default -1, to use maintenance_work_mem Maximum amount of memory used by each autovacuum worker.

 

PostgreSQL DBA part 3 :-How to start/stop postgresql database

Posted on by debasis maity

A.Stopping postgresql database

pg_ctl can be used to stop a database cluster

•pg_ctl supports three modes of shutdown
−smart quit after all clients have disconnected
−fast (default)quit directly, with proper shutdown
−immediate quit without complete shutdown; will lead to recovery

•Syntax
−pg_ctl stop [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]

-bash-4.1$ pwd
/opt/edb/as9.6/bin
-bash-4.1$ ./pg_ctl -D /opt/edb/as9.6/data -mf stop
waiting for server to shut down.... done
server stopped
-bash-4.1$ ps -ef|grep postgresql
54322 12838 12225 0 09:30 pts/2 00:00:00 grep postgresql

B.Starting postgresql database

pg_ctl can be used to start a database cluster

Choose a unique port for postmaster
•Change the port in postgresql.conf

•pg_ctl utility can be used to start a cluster
•Syntax
−pg_ctl start [options]
−Options
-D location of the database storage area
-l write (or append) server log to FILENAME
-w wait until operation completes
-t seconds to wait when using -w option

-bash-4.1$ ./pg_ctl -D /opt/edb/as9.6/data -mf start
server starting
-bash-4.1$ 2018-05-08 09:30:19 IST LOG: redirecting log output to logging collector process
2018-05-08 09:30:19 IST HINT: Future log output will appear in directory "pg_log".
-bash-4.1$ ps -ef|grep post
root 1947 1 0 May07 ? 00:00:00 /usr/libexec/postfix/master
postfix 1956 1947 0 May07 ? 00:00:00 qmgr -l -t fifo -u
postfix 12663 1947 0 09:13 ? 00:00:00 pickup -l -t fifo -u
54322 12841 1 0 09:30 pts/2 00:00:00 /opt/edb/as9.6/bin/edb-postgres -D /opt/edb/as9.6/data
54322 12842 12841 0 09:30 ? 00:00:00 postgres: logger process
54322 12844 12841 0 09:30 ? 00:00:00 postgres: checkpointer process
54322 12845 12841 0 09:30 ? 00:00:00 postgres: writer process
54322 12846 12841 0 09:30 ? 00:00:00 postgres: wal writer process
54322 12847 12841 0 09:30 ? 00:00:00 postgres: autovacuum launcher process
54322 12848 12841 0 09:30 ? 00:00:00 postgres: stats collector process
54322 12849 12841 0 09:30 ? 00:00:00 postgres: bgworker: dbms_aq launcher

C.pg_controldatacan be used to view the control information for a database cluster

-bash-4.1$ ./pg_controldata /opt/edb/as9.6/data
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6551197085840965172
Database cluster state: in production
pg_control last modified: Tue 08 May 2018 09:30:19 AM IST
Latest checkpoint location: 0/274D828
Prior checkpoint location: 0/274D7B8
Latest checkpoint's REDO location: 0/274D828
Latest checkpoint's REDO WAL file: 000000010000000000000002
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:2347
Latest checkpoint's NextOID: 24581
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 2326
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Tue 08 May 2018 09:29:55 AM IST
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: minimal
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0

PostgreSQL DBA part2 :-How to connect postgresql database

Posted on by debasis maity

A.Connecting postgresql locally using port

Please identify port in postgresql.conf under /opt/edb/as9.6/data and connect using psql

There are pre-requisites for allowing local connection.This pg_hba.conf file is under  /opt/edb/as9.6/data.Please make sure last parameter should be md5 not peer.

The below content is for example what should be in pg_hba.conf

# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5

cd /opt/edb/as9.6/bin

-bash-4.1$ ./psql -p 5444 edb enterprisedb
 psql.bin (9.6.2.7)
 Type "help" for help.
port:-5444
Database name:-edb
Password for enterprisedb user:-enterprisedb

B.Allowing remote connections

  1. As a super user, open /etc/postgresql/9.6/main/pg_hba.conf (Ubuntu) or /var/lib/pgsql/9.6/data/pg_hba.conf (Red Hat) in a text editor.

  2. Scroll down to the line that describes local socket connections. It may look like this:

    local   all             all                                      peer

  3. Change to:

    host    all             all             0.0.0.0/0               trust

    1. Save and close the file.

    2. In the same directory, open postgresql.conf.

Under the section on Connection Settings, add or replace the line that starts with listen_addresses to respond to all requests:

listen_addresses = '*'

Please restart postgresql service now.

C.Use psql command from client system as follows(Before that you need to setup pg_hba.conf as above to allow remote connection)

psql -h PostgreSQL-IP-ADDRESS -U USERNAME -d DATABASENAME

Connect to remote server by IP address 192.168.1.5 and login using debasis user to connect to edb database, use:
$ psql -h 192.168.1.5 -U debasis -d edb
Where,

  • -h 192.168.1.5 : Specifies the host name of the machine or IP address (192.168.1.5) on which the server is running.
  • -U debasis : Connect to the database as the debasis username instead of the default. You must have account and permission to connect as debasis user.
  • -d edb : Specifies the name of the database (edb) to connect to.

D.Connect to edb sql

cd /opt/edb/as9.6/bin

-bash-4.1$ ./edb-psql edb
psql.bin (9.6.2.7)
Type “help” for help.

edb=#

Here edb is database name.

 

 

 

PostgreSQL DBA part1 :-Installation of PostgreSQL 9.6 in Linux

Posted on by debasis maity

I will show how you can install PostgreSQL  9.6 in Linux 7.This document is based on guidance provided by EDB (Enterprise distributor of PostgreSQL)

Step 1:-First we need to check Supported platform as below

The Advanced Server 9.6 RPM packages are supported on the following platforms:
64 bit Linux:

Red Hat Enterprise Linux 6.x and 7.x

CentOS 6.x and 7.x

SLES 12

The Advanced Server 9.6 graphical (or interactive) installers are supported on the following platforms:

64 bit Linux:

Red Hat Enterprise Linux 6.x and 7.x

CentOS 6.x and 7.x

Oracle Enterprise Linux 6.x and 7.x

Ubuntu 14.04 LTS and 16.04 LTS

Debian 7 and 8

SELinux Enterprise 12.x

64 bit Windows:

Windows Server 2016

Windows Server 2012 R2 Server

Windows Server 2008 R2 Server

The Connectors (JDBC/.NET/ODBC/OCL) are supported on Windows 7 (32 bit client), Windows 8 and Windows 10 clients.

Step 2:-Let me  Change SELinux to permissive mode:

[root@debasiseric4 ~]# vi /etc/selinux/config
#SELINUX=enforcing
SELINUX=permissive

[root@debasiseric4 ~]# sestatus
SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: enforcing
Mode from config file: permissive
Policy MLS status: enabled
Policy deny_unknown status: allowed
Max kernel policy version: 31
[root@debasiseric4 ~]# setenforce Permissive
[root@debasiseric4 ~]# sestatus
SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: permissive
Mode from config file: permissive
Policy MLS status: enabled
Policy deny_unknown status: allowed
Max kernel policy version: 31

Step 3:-Now I will download PostgreSQL 9.6 from EDB

https://www.enterprisedb.com/enterprisedb-software-downloads

Please save the file to the local drive and ftp to server where postgreSQL needs to be installed.

 

Step 4:-Please download JDK 1.8 and transfer to Linux host.After that please install/untar JDK

Step 5:-Now I will install postgreSQL

A.Please provide execute permission to root
[root@debasiseric4 user]# ./edb-as96-meta-9.6.2.7-2-linux-x64.run
-bash: ./edb-as96-meta-9.6.2.7-2-linux-x64.run: Permission denied
[root@debasiseric4 user]# chmod 777 edb-as96-meta-9.6.2.7-2-linux-x64.run
B.Please export new JDK 8 home.
[root@debasiseric4 jdk1.8.0_151]# export PATH=/home/user/jdk1.8.0_151/bin:$PATH
[root@debasiseric4 jdk1.8.0_151]# which java
/home/user/jdk1.8.0_151/bin/java
[root@debasiseric4 jdk1.8.0_151]# java -version
java version “1.8.0_151”
C.Now continue the installation
[root@debasiseric4 user]# ./edb-as96-meta-9.6.2.7-2-linux-x64.run
Language Selection  Please select the installation language
[1] English – English
[2] Japanese – 日本語
[3] Simplified Chinese – 简体中文
[4] Traditional Chinese – 繁体中文
[5] Korean – 한국어
Please choose an option [1] :
Please choose an option [1] :
—————————————————————————-
Welcome to the EDB Postgres Advanced Server Setup Wizard.
—————————————————————————-
Please read the following License Agreement. You must accept the terms of this
agreement before continuing with the installation.
Press [Enter] to continue:
Limited Use Software License Agreement
Version 2.9
Do you accept this license? [y/n]: y
—————————————————————————-
User Authentication
This installation requires a registration with EnterpriseDB.com. Please enter
your credentials below. If you do not have an account, Please create one now on
https://www.enterprisedb.com/user-login-registration

Email [debasis.tcs@gmail.com]:

Password :

—————————————————————————-
Please specify the directory where EDB Postgres Advanced Server will be
installed.

Installation Directory [/opt/edb]:

—————————————————————————-
Select the components you want to install.

EDB Postgres Advanced Server [Y/n] :Y

EDB Postgres Connectors [Y/n] :Y

EDB Postgres Advanced Server Infinite Cache [Y/n] :Y

EDB Postgres Migration Toolkit [Y/n] :Y

EDB Postgres pgAdmin 4 [Y/n] :Y

EDB Postgres pgPool-II [Y/n] :Y

EDB Postgres pgPool-II Extensions [Y/n] :Y

EDB Postgres Advanced Server EDB*Plus [Y/n] :Y

EDB Postgres Advanced Server Slony Replication [Y/n] :Y

EDB Postgres pgBouncer [Y/n] :Y

Is the selection above correct? [Y/n]: Y

—————————————————————————-
Additional Directories

Please select a directory under which to store your data.

Data Directory [/opt/edb/as9.6/data]:

Please select a directory under which to store your Write-Ahead Logs.

Write-Ahead Log (WAL) Directory [/opt/edb/as9.6/data/pg_xlog]:

—————————————————————————-
Configuration Mode

EDB Postgres Advanced Server always installs with database compatibility features for Oracle(R) and maintains full PostgreSQL compliance. Select your style preference for installation defaults and samples.

The Oracle configuration will cause the use of certain objects (e.g. DATE data types, string operations, etc.) to produce results compatible with Oracle, create the same Oracle sample tables, and have the database match Oracle examples used in the documentation.

Configuration Mode

[1] Compatible with Oracle
[2] Compatible with PostgreSQL
Please choose an option [1] : 1

—————————————————————————-
Please provide a password for the database superuser (enterprisedb). A locked
Unix user account (enterprisedb) will be created if not present.

Password :
Retype Password :
—————————————————————————-
Additional Configuration

Please select the port number the server should listen on.

Port [5444]:

Select the locale to be used by the new database cluster.

Locale

[1] [Default locale]
[2] aa_DJ
[3] aa_DJ.iso88591
[4] aa_DJ.utf8
[5] aa_ER
[6] aa_ER@saaho
[7] aa_ER.utf8
[8] aa_ER.utf8@saaho
[9] aa_ET
[10] aa_ET.utf8
[11] af_ZA
[12] af_ZA.iso88591
[13] af_ZA.utf8
[14] am_ET
[15] am_ET.utf8
[16] an_ES
[17] an_ES.iso885915
[18] an_ES.utf8
[19] ar_AE
[20] ar_AE.iso88596
[21] ar_AE.utf8
[22] ar_BH
[23] ar_BH.iso88596
[24] ar_BH.utf8
[25] ar_DZ
[26] ar_DZ.iso88596
[27] ar_DZ.utf8
[28] ar_EG
[29] ar_EG.iso88596
[30] ar_EG.utf8
[31] ar_IN
[32] ar_IN.utf8
[33] ar_IQ
[34] ar_IQ.iso88596
[35] ar_IQ.utf8
[36] ar_JO
[37] ar_JO.iso88596
[38] ar_JO.utf8
[39] ar_KW
[40] ar_KW.iso88596
[41] ar_KW.utf8
[42] ar_LB
[43] ar_LB.iso88596
[44] ar_LB.utf8
[45] ar_LY
[46] ar_LY.iso88596
[47] ar_LY.utf8
[48] ar_MA
[49] ar_MA.iso88596
[50] ar_MA.utf8
[51] ar_OM
[52] ar_OM.iso88596
[53] ar_OM.utf8
[54] ar_QA
[55] ar_QA.iso88596
[56] ar_QA.utf8
[57] ar_SA
[58] ar_SA.iso88596
[59] ar_SA.utf8
[60] ar_SD
[61] ar_SD.iso88596
[62] ar_SD.utf8
[63] ar_SY
[64] ar_SY.iso88596
[65] ar_SY.utf8
[66] ar_TN
[67] ar_TN.iso88596
[68] ar_TN.utf8
[69] ar_YE
[70] ar_YE.iso88596
[71] ar_YE.utf8
[72] as_IN
[73] as_IN.utf8
[74] ast_ES
[75] ast_ES.iso885915
[76] ast_ES.utf8
[77] ayc_PE
[78] ayc_PE.utf8
[79] az_AZ
[80] az_AZ.utf8
[81] be_BY
[82] be_BY.cp1251
[83] be_BY@latin
[84] be_BY.utf8
[85] be_BY.utf8@latin
[86] bem_ZM
[87] bem_ZM.utf8
[88] ber_DZ
[89] ber_DZ.utf8
[90] ber_MA
[91] ber_MA.utf8
[92] bg_BG
[93] bg_BG.cp1251
[94] bg_BG.utf8
[95] bho_IN
[96] bho_IN.utf8
[97] bn_BD
[98] bn_BD.utf8
[99] bn_IN
[100] bn_IN.utf8
[101] bo_CN
[102] bo_CN.utf8
[103] bo_IN
[104] bo_IN.utf8
[105] bokmal
[106] bokmål
[107] br_FR
[108] br_FR@euro
[109] br_FR.iso88591
[110] br_FR.iso885915@euro
[111] br_FR.utf8
[112] brx_IN
[113] brx_IN.utf8
[114] bs_BA
[115] bs_BA.iso88592
[116] bs_BA.utf8
[117] byn_ER
[118] byn_ER.utf8
[119] C
[120] ca_AD
[121] ca_AD.iso885915
[122] ca_AD.utf8
[123] ca_ES
[124] ca_ES@euro
[125] ca_ES.iso88591
[126] ca_ES.iso885915@euro
[127] ca_ES.utf8
[128] ca_FR
[129] ca_FR.iso885915
[130] ca_FR.utf8
[131] ca_IT
[132] ca_IT.iso885915
[133] ca_IT.utf8
[134] catalan
[135] crh_UA
[136] crh_UA.utf8
[137] croatian
[138] csb_PL
[139] csb_PL.utf8
[140] cs_CZ
[141] cs_CZ.iso88592
[142] cs_CZ.utf8
[143] cv_RU
[144] cv_RU.utf8
[145] cy_GB
[146] cy_GB.iso885914
[147] cy_GB.utf8
[148] czech
[149] da_DK
[150] da_DK.iso88591
[151] da_DK.iso885915
[152] da_DK.utf8
[153] danish
[154] dansk
[155] de_AT
[156] de_AT@euro
[157] de_AT.iso88591
[158] de_AT.iso885915@euro
[159] de_AT.utf8
[160] de_BE
[161] de_BE@euro
[162] de_BE.iso88591
[163] de_BE.iso885915@euro
[164] de_BE.utf8
[165] de_CH
[166] de_CH.iso88591
[167] de_CH.utf8
[168] de_DE
[169] de_DE@euro
[170] de_DE.iso88591
[171] de_DE.iso885915@euro
[172] de_DE.utf8
[173] de_LU
[174] de_LU@euro
[175] de_LU.iso88591
[176] de_LU.iso885915@euro
[177] de_LU.utf8
[178] deutsch
[179] doi_IN
[180] doi_IN.utf8
[181] dutch
[182] dv_MV
[183] dv_MV.utf8
[184] dz_BT
[185] dz_BT.utf8
[186] eesti
[187] el_CY
[188] el_CY.iso88597
[189] el_CY.utf8
[190] el_GR
[191] el_GR.iso88597
[192] el_GR.utf8
[193] en_AG
[194] en_AG.utf8
[195] en_AU
[196] en_AU.iso88591
[197] en_AU.utf8
[198] en_BW
[199] en_BW.iso88591
[200] en_BW.utf8
[201] en_CA
[202] en_CA.iso88591
[203] en_CA.utf8
[204] en_DK
[205] en_DK.iso88591
[206] en_DK.utf8
[207] en_GB
[208] en_GB.iso88591
[209] en_GB.iso885915
[210] en_GB.utf8
[211] en_HK
[212] en_HK.iso88591
[213] en_HK.utf8
[214] en_IE
[215] en_IE@euro
[216] en_IE.iso88591
[217] en_IE.iso885915@euro
[218] en_IE.utf8
[219] en_IN
[220] en_IN.utf8
[221] en_NG
[222] en_NG.utf8
[223] en_NZ
[224] en_NZ.iso88591
[225] en_NZ.utf8
[226] en_PH
[227] en_PH.iso88591
[228] en_PH.utf8
[229] en_SG
[230] en_SG.iso88591
[231] en_SG.utf8
[232] en_US
[233] en_US.iso88591
[234] en_US.iso885915
[235] en_US.utf8
[236] en_ZA
[237] en_ZA.iso88591
[238] en_ZA.utf8
[239] en_ZM
[240] en_ZM.utf8
[241] en_ZW
[242] en_ZW.iso88591
[243] en_ZW.utf8
[244] es_AR
[245] es_AR.iso88591
[246] es_AR.utf8
[247] es_BO
[248] es_BO.iso88591
[249] es_BO.utf8
[250] es_CL
[251] es_CL.iso88591
[252] es_CL.utf8
[253] es_CO
[254] es_CO.iso88591
[255] es_CO.utf8
[256] es_CR
[257] es_CR.iso88591
[258] es_CR.utf8
[259] es_CU
[260] es_CU.utf8
[261] es_DO
[262] es_DO.iso88591
[263] es_DO.utf8
[264] es_EC
[265] es_EC.iso88591
[266] es_EC.utf8
[267] es_ES
[268] es_ES@euro
[269] es_ES.iso88591
[270] es_ES.iso885915@euro
[271] es_ES.utf8
[272] es_GT
[273] es_GT.iso88591
[274] es_GT.utf8
[275] es_HN
[276] es_HN.iso88591
[277] es_HN.utf8
[278] es_MX
[279] es_MX.iso88591
[280] es_MX.utf8
[281] es_NI
[282] es_NI.iso88591
[283] es_NI.utf8
[284] es_PA
[285] es_PA.iso88591
[286] es_PA.utf8
[287] es_PE
[288] es_PE.iso88591
[289] es_PE.utf8
[290] es_PR
[291] es_PR.iso88591
[292] es_PR.utf8
[293] es_PY
[294] es_PY.iso88591
[295] es_PY.utf8
[296] es_SV
[297] es_SV.iso88591
[298] es_SV.utf8
[299] estonian
[300] es_US
[301] es_US.iso88591
[302] es_US.utf8
[303] es_UY
[304] es_UY.iso88591
[305] es_UY.utf8
[306] es_VE
[307] es_VE.iso88591
[308] es_VE.utf8
[309] et_EE
[310] et_EE.iso88591
[311] et_EE.iso885915
[312] et_EE.utf8
[313] eu_ES
[314] eu_ES@euro
[315] eu_ES.iso88591
[316] eu_ES.iso885915@euro
[317] eu_ES.utf8
[318] fa_IR
[319] fa_IR.utf8
[320] ff_SN
[321] ff_SN.utf8
[322] fi_FI
[323] fi_FI@euro
[324] fi_FI.iso88591
[325] fi_FI.iso885915@euro
[326] fi_FI.utf8
[327] fil_PH
[328] fil_PH.utf8
[329] finnish
[330] fo_FO
[331] fo_FO.iso88591
[332] fo_FO.utf8
[333] français
[334] fr_BE
[335] fr_BE@euro
[336] fr_BE.iso88591
[337] fr_BE.iso885915@euro
[338] fr_BE.utf8
[339] fr_CA
[340] fr_CA.iso88591
[341] fr_CA.utf8
[342] fr_CH
[343] fr_CH.iso88591
[344] fr_CH.utf8
[345] french
[346] fr_FR
[347] fr_FR@euro
[348] fr_FR.iso88591
[349] fr_FR.iso885915@euro
[350] fr_FR.utf8
[351] fr_LU
[352] fr_LU@euro
[353] fr_LU.iso88591
[354] fr_LU.iso885915@euro
[355] fr_LU.utf8
[356] fur_IT
[357] fur_IT.utf8
[358] fy_DE
[359] fy_DE.utf8
[360] fy_NL
[361] fy_NL.utf8
[362] ga_IE
[363] ga_IE@euro
[364] ga_IE.iso88591
[365] ga_IE.iso885915@euro
[366] ga_IE.utf8
[367] galego
[368] galician
[369] gd_GB
[370] gd_GB.iso885915
[371] gd_GB.utf8
[372] german
[373] gez_ER
[374] gez_ER@abegede
[375] gez_ER.utf8
[376] gez_ER.utf8@abegede
[377] gez_ET
[378] gez_ET@abegede
[379] gez_ET.utf8
[380] gez_ET.utf8@abegede
[381] gl_ES
[382] gl_ES@euro
[383] gl_ES.iso88591
[384] gl_ES.iso885915@euro
[385] gl_ES.utf8
[386] greek
[387] gu_IN
[388] gu_IN.utf8
[389] gv_GB
[390] gv_GB.iso88591
[391] gv_GB.utf8
[392] ha_NG
[393] ha_NG.utf8
[394] hebrew
[395] he_IL
[396] he_IL.iso88598
[397] he_IL.utf8
[398] hi_IN
[399] hi_IN.utf8
[400] hne_IN
[401] hne_IN.utf8
[402] hr_HR
[403] hr_HR.iso88592
[404] hr_HR.utf8
[405] hrvatski
[406] hsb_DE
[407] hsb_DE.iso88592
[408] hsb_DE.utf8
[409] ht_HT
[410] ht_HT.utf8
[411] hu_HU
[412] hu_HU.iso88592
[413] hu_HU.utf8
[414] hungarian
[415] hy_AM
[416] hy_AM.utf8
[417] ia_FR
[418] ia_FR.utf8
[419] icelandic
[420] id_ID
[421] id_ID.iso88591
[422] id_ID.utf8
[423] ig_NG
[424] ig_NG.utf8
[425] ik_CA
[426] ik_CA.utf8
[427] is_IS
[428] is_IS.iso88591
[429] is_IS.utf8
[430] italian
[431] it_CH
[432] it_CH.iso88591
[433] it_CH.utf8
[434] it_IT
[435] it_IT@euro
[436] it_IT.iso88591
[437] it_IT.iso885915@euro
[438] it_IT.utf8
[439] iu_CA
[440] iu_CA.utf8
[441] iw_IL
[442] iw_IL.iso88598
[443] iw_IL.utf8
[444] ja_JP
[445] ja_JP.eucjp
[446] ja_JP.ujis
[447] ja_JP.utf8
[448] japanese
[449] japanese.euc
[450] ka_GE.utf8
[451] kk_KZ.utf8
[452] kl_GL
[453] kl_GL.iso88591
[454] kl_GL.utf8
[455] km_KH
[456] km_KH.utf8
[457] kn_IN
[458] kn_IN.utf8
[459] kok_IN
[460] kok_IN.utf8
[461] ko_KR
[462] ko_KR.euckr
[463] ko_KR.utf8
[464] korean
[465] korean.euc
[466] ks_IN
[467] ks_IN@devanagari
[468] ks_IN.utf8
[469] ks_IN.utf8@devanagari
[470] ku_TR
[471] ku_TR.iso88599
[472] ku_TR.utf8
[473] kw_GB
[474] kw_GB.iso88591
[475] kw_GB.utf8
[476] ky_KG
[477] ky_KG.utf8
[478] lb_LU
[479] lb_LU.utf8
[480] lg_UG
[481] lg_UG.iso885910
[482] lg_UG.utf8
[483] li_BE
[484] li_BE.utf8
[485] lij_IT
[486] lij_IT.utf8
[487] li_NL
[488] li_NL.utf8
[489] lithuanian
[490] lo_LA
[491] lo_LA.utf8
[492] lt_LT
[493] lt_LT.iso885913
[494] lt_LT.utf8
[495] lv_LV
[496] lv_LV.iso885913
[497] lv_LV.utf8
[498] mag_IN
[499] mag_IN.utf8
[500] mai_IN
[501] mai_IN.utf8
[502] mg_MG
[503] mg_MG.iso885915
[504] mg_MG.utf8
[505] mhr_RU
[506] mhr_RU.utf8
[507] mi_NZ
[508] mi_NZ.iso885913
[509] mi_NZ.utf8
[510] mk_MK
[511] mk_MK.iso88595
[512] mk_MK.utf8
[513] ml_IN
[514] ml_IN.utf8
[515] mni_IN
[516] mni_IN.utf8
[517] mn_MN
[518] mn_MN.utf8
[519] mr_IN
[520] mr_IN.utf8
[521] ms_MY
[522] ms_MY.iso88591
[523] ms_MY.utf8
[524] mt_MT
[525] mt_MT.iso88593
[526] mt_MT.utf8
[527] my_MM
[528] my_MM.utf8
[529] nan_TW@latin
[530] nan_TW.utf8@latin
[531] nb_NO
[532] nb_NO.iso88591
[533] nb_NO.utf8
[534] nds_DE
[535] nds_DE.utf8
[536] nds_NL
[537] nds_NL.utf8
[538] ne_NP
[539] ne_NP.utf8
[540] nhn_MX
[541] nhn_MX.utf8
[542] niu_NU
[543] niu_NU.utf8
[544] niu_NZ
[545] niu_NZ.utf8
[546] nl_AW
[547] nl_AW.utf8
[548] nl_BE
[549] nl_BE@euro
[550] nl_BE.iso88591
[551] nl_BE.iso885915@euro
[552] nl_BE.utf8
[553] nl_NL
[554] nl_NL@euro
[555] nl_NL.iso88591
[556] nl_NL.iso885915@euro
[557] nl_NL.utf8
[558] nn_NO
[559] nn_NO.iso88591
[560] nn_NO.utf8
[561] no_NO
[562] no_NO.ISO-8859-1
[563] norwegian
[564] nr_ZA
[565] nr_ZA.utf8
[566] nso_ZA
[567] nso_ZA.utf8
[568] nynorsk
[569] oc_FR
[570] oc_FR.iso88591
[571] oc_FR.utf8
[572] om_ET
[573] om_ET.utf8
[574] om_KE
[575] om_KE.iso88591
[576] om_KE.utf8
[577] or_IN
[578] or_IN.utf8
[579] os_RU
[580] os_RU.utf8
[581] pa_IN
[582] pa_IN.utf8
[583] pap_AN
[584] pap_AN.utf8
[585] pa_PK
[586] pa_PK.utf8
[587] pl_PL
[588] pl_PL.iso88592
[589] pl_PL.utf8
[590] polish
[591] portuguese
[592] POSIX
[593] ps_AF
[594] ps_AF.utf8
[595] pt_BR
[596] pt_BR.iso88591
[597] pt_BR.utf8
[598] pt_PT
[599] pt_PT@euro
[600] pt_PT.iso88591
[601] pt_PT.iso885915@euro
[602] pt_PT.utf8
[603] romanian
[604] ro_RO
[605] ro_RO.iso88592
[606] ro_RO.utf8
[607] ru_RU
[608] ru_RU.iso88595
[609] ru_RU.koi8r
[610] ru_RU.utf8
[611] russian
[612] ru_UA
[613] ru_UA.koi8u
[614] ru_UA.utf8
[615] rw_RW
[616] rw_RW.utf8
[617] sa_IN
[618] sa_IN.utf8
[619] sat_IN
[620] sat_IN.utf8
[621] sc_IT
[622] sc_IT.utf8
[623] sd_IN
[624] sd_IN@devanagari
[625] sd_IN.utf8
[626] sd_IN.utf8@devanagari
[627] se_NO
[628] se_NO.utf8
[629] shs_CA
[630] shs_CA.utf8
[631] sid_ET
[632] sid_ET.utf8
[633] si_LK
[634] si_LK.utf8
[635] sk_SK
[636] sk_SK.iso88592
[637] sk_SK.utf8
[638] slovak
[639] slovene
[640] slovenian
[641] sl_SI
[642] sl_SI.iso88592
[643] sl_SI.utf8
[644] so_DJ
[645] so_DJ.iso88591
[646] so_DJ.utf8
[647] so_ET
[648] so_ET.utf8
[649] so_KE
[650] so_KE.iso88591
[651] so_KE.utf8
[652] so_SO
[653] so_SO.iso88591
[654] so_SO.utf8
[655] spanish
[656] sq_AL
[657] sq_AL.iso88591
[658] sq_AL.utf8
[659] sq_MK
[660] sq_MK.utf8
[661] sr_ME
[662] sr_ME.utf8
[663] sr_RS
[664] sr_RS@latin
[665] sr_RS.utf8
[666] sr_RS.utf8@latin
[667] ss_ZA
[668] ss_ZA.utf8
[669] st_ZA
[670] st_ZA.iso88591
[671] st_ZA.utf8
[672] sv_FI
[673] sv_FI@euro
[674] sv_FI.iso88591
[675] sv_FI.iso885915@euro
[676] sv_FI.utf8
[677] sv_SE
[678] sv_SE.iso88591
[679] sv_SE.iso885915
[680] sv_SE.utf8
[681] swedish
[682] sw_KE
[683] sw_KE.utf8
[684] sw_TZ
[685] sw_TZ.utf8
[686] szl_PL
[687] szl_PL.utf8
[688] ta_IN
[689] ta_IN.utf8
[690] ta_LK
[691] ta_LK.utf8
[692] te_IN
[693] te_IN.utf8
[694] tg_TJ.utf8
[695] th_TH.utf8
[696] ti_ER
[697] ti_ER.utf8
[698] ti_ET
[699] ti_ET.utf8
[700] tig_ER
[701] tig_ER.utf8
[702] tk_TM
[703] tk_TM.utf8
[704] tl_PH
[705] tl_PH.iso88591
[706] tl_PH.utf8
[707] tn_ZA
[708] tn_ZA.utf8
[709] tr_CY
[710] tr_CY.iso88599
[711] tr_CY.utf8
[712] tr_TR
[713] tr_TR.iso88599
[714] tr_TR.utf8
[715] ts_ZA
[716] ts_ZA.utf8
[717] tt_RU
[718] tt_RU@iqtelif
[719] tt_RU.utf8
[720] tt_RU.utf8@iqtelif
[721] turkish
[722] ug_CN
[723] ug_CN.utf8
[724] uk_UA
[725] uk_UA.koi8u
[726] uk_UA.utf8
[727] unm_US
[728] unm_US.utf8
[729] ur_IN
[730] ur_IN.utf8
[731] ur_PK
[732] ur_PK.utf8
[733] uz_UZ
[734] uz_UZ@cyrillic
[735] uz_UZ.iso88591
[736] uz_UZ.utf8@cyrillic
[737] ve_ZA
[738] ve_ZA.utf8
[739] vi_VN
[740] vi_VN.utf8
[741] wa_BE
[742] wa_BE@euro
[743] wa_BE.iso88591
[744] wa_BE.iso885915@euro
[745] wa_BE.utf8
[746] wae_CH
[747] wae_CH.utf8
[748] wal_ET
[749] wal_ET.utf8
[750] wo_SN
[751] wo_SN.utf8
[752] xh_ZA
[753] xh_ZA.iso88591
[754] xh_ZA.utf8
[755] yi_US
[756] yi_US.cp1255
[757] yi_US.utf8
[758] yo_NG
[759] yo_NG.utf8
[760] yue_HK
[761] yue_HK.utf8
[762] zh_CN
[763] zh_CN.gb2312
[764] zh_CN.utf8
[765] zh_HK.utf8
[766] zh_SG
[767] zh_SG.gb2312
[768] zh_SG.utf8
[769] zh_TW.euctw
[770] zh_TW.utf8
[771] zu_ZA
[772] zu_ZA.iso88591
[773] zu_ZA.utf8
Please choose an option [1] :

Install sample tables and procedures. [Y/n]: Y

—————————————————————————-
Dynatune Dynamic Tuning:
Server Utilization

Please select the type of server to determine the amount of system resources
that may be utilized:

[1] Development (e.g. a developer’s laptop)
[2] General Purpose (e.g. a web or application server)
[3] Dedicated (a server running only EDB Postgres)
Please choose an option [2] :

—————————————————————————-
Dynatune Dynamic Tuning:
Workload Profile

Please select the type of workload this server will be used for:

[1] Transaction Processing (OLTP systems)
[2] General Purpose (OLTP and reporting workloads)
[3] Reporting (Complex queries or OLAP workloads)
Please choose an option [1] :

—————————————————————————-
Advanced Configuration

—————————————————————————-
PgBouncer Listening Port [6432]:

—————————————————————————-
Service Configuration

Autostart PgBouncer Service [Y/n]:Y

Autostart pgAgent Service [Y/n]:Y

Update Notification Service [Y/n]: Y

The Update Notification Service informs, downloads and installs whenever
security patches and other updates are available for your EDB Postgres Advanced
Server installation.

—————————————————————————-
Pre Installation Summary

Following settings will be used for installation:
Data Directory [/opt/edb/as9.6/data]:
Installation Directory: /opt/edb
Data Directory: /opt/edb/as9.6/data
WAL Directory: /opt/edb/as9.6/data/pg_xlog
Database Port: 5444
Database Superuser: enterprisedb
Operating System Account: enterprisedb
Database Service: edb-as-9.6
PgBouncer Listening Port: 6432
Press [Enter] to continue:
—————————————————————————-
Setup is now ready to begin installing EDB Postgres Advanced Server on your
computer.
Do you want to continue? [Y/n]: Y
—————————————————————————-
Please wait while Setup installs EDB Postgres Advanced Server on your computer.
Installing EDB Postgres Advanced Server
0% ______________ 50% ______________ 100%
########################################
Installing EDB Postgres Advanced Server …
Installing EDB Postgres Advanced Server pgAgent …
Installing EDB Postgres Connectors …
Installing EDB Postgres Migration Toolkit …
Installing EDB Postgres Advanced Server EDB*Plus …
Installing EDB Postgres Advanced Server Infinite Cache …
Installing EDB Postgres pgAdmin 4 …
Installing EDB Postgres Advanced Server Slony Replication …
Installing EDB Postgres pgPool-II …
Installing EDB Postgres pgPool-II Extensions …

Step 6:-Now I will do post Installation Steps:

A. Change the ownership of edb to enterprisedb:enterprisedb

[root@debasiseric4 ]# cd /opt/
[root@debasiseric4 opt]# chown -R enterprisedb.enterprisedb edb/
[root@debasiseric4 opt]# ll
total 8
drwxr-xr-x. 9 enterprisedb enterprisedb 4096 Mar 24 14:36 edb

B. Set profile

-bash-4.1$ pwd
/opt/edb/as9.6
-bash-4.1$ cat pgplus_env.sh

# Environment

export PATH=/opt/edb/as9.6/bin:$PATH
export EDBHOME=/opt/edb/as9.6
export PGDATA=/opt/edb/as9.6/data
export PGDATABASE=edb
# export PGUSER=enterprisedb
export PGPORT=5444
export PGLOCALEDIR=/opt/edb/as9.6/share/locale

-bash-4.1$ cat .profile
. pgplus_env.sh
-bash-4.1$ pwd
/opt/PostgresPlus/9.5AS

3. Stop and Start the pg_ctl server

-bash-4.1$ pg_ctl -D $PGDATA stop -mf
[root@debasiseric4 ~]# /etc/init.d/ppas-9.6 stop -mf

[root@debasiseric4 ~]# pg_ctl -D $PGDATA start -w
-bash-4.1$ /etc/init.d/ppas-9.6 start
pg_ctl: server is running (PID: 4869)
/opt/edb/as9.6/bin/edb-postgres “-D” “/opt/PostgresPlus/9.5AS/data”
-bash-4.1$ /etc/init.d/ppas-9.6 status
INFO: [PID: 4869]
INFO: [CMD:]
MSG: [ppas-9.6 is running]
INFO: [Please see service script file /var/log/ppas-9.6/ppas-9.6_script.log for details]

 

PostgreSQL Database Backup to S3 and Restore from S3 using simple shell script

Posted on by samindradey

Following topic will discuss how to Backup and Restore PostgreSQL Databases directly in S3. This topic will help you to leverage AWS S3 -IA storage to use as Backup Storage for any number of PostgreSQL databases running under an instance.

This topic will cover PostgreSQL Instance running on EC2 or on-premises servers.

Step 1:- Create a new mount point or directory in the database server to use as staging location for placing the backup files before moving into S3.

Example:

 

mkdir /postgresql_bkp

chown postgres:postgres /postgresql_bkp

chmod -R 700 /postgresql_bkp

Step 2:- Now Create a bucket in S3 and inside the bucket create a directory.

Example: s3://phi-postgresql-backup/postgresql01-pgbackup

Step 3:- Now install AWS CLI in the DB server under postgres user following AWS recommended guide:-

http://docs.aws.amazon.com/cli/latest/userguide/awscli-install-linux.html

Configure AWS CLI

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

Step 4:- Now configure password of postgres user so during pg_dump or psql using script, the password of the user can be taken from the environment variable.

In postgres user home, create a file .pgpass

vi $HOME/.pgpass

localhost:5432:postgres:postgres:somepassword

In .bash_profile make following entry:–

vi $HOME/.bash_profile

[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/9.6/data
export PGDATA
PATH=$PATH:/usr/pgsql-9.6/bin
export PATH
PGPASSFILE=/var/lib/pgsql/.pgpass
export PGPASSFILE
PGPASSWORD=somepassword
export PGPASSWORD
SCRIPTS=/var/lib/pgsql/9.6/data/scripts
export SCRIPTS
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
export PATH=~/.local/bin:$PATH

Step 5:- Now we will create the scripts to backup the postgresql databases.

In Step 4, we configure $SCRIPT to point a directory /var/lib/pgsql/9.6/data/scripts where we will place our scripts.

The backup script will do the following:-

=> Backup all the databases running in the postgresql instance.

=> Move the backup files to S3 bucket in S3 – IA storage class one after another and remove the backup file from the local storage.

=> Delete backup files older than 7 days from the S3 bucket.

cd $SCRIPTS

vi pg_dump_s3.sh

#!/bin/bash

set -e

# Database credentials
PG_HOST=”localhost”
PG_USER=”postgres”

# S3
S3_PATH=”phi-postgresql-backup/postgresql01-pgbackup”

# get databases list
dbs=`psql -l -t | cut -d’|’ -f1 | sed -e ‘s/ //g’ -e ‘/^$/d’`

# Vars
NOW=$(date +”%m-%d-%Y-at-%H-%M-%S”)
#DIR=”$( cd “$( dirname “${BASH_SOURCE[0]}” )” && pwd )”
DIR=”/postgresql_bkp/pgsql_backup_logical”
SCRIPT=”/var/lib/pgsql/9.6/data/scripts”
echo “PostgreSQL Database Logical Backup is started and stored in s3://phi-postgresql-backup/postgresql01-pgbackup”
for db in $dbs; do
if [ “$db” != “template0” ] && [ “$db” != “template1″ ]; then
# Dump database
pg_dump -Fc -h $PG_HOST -U $PG_USER $db > $DIR/”$NOW”_”$db”.dump
# Copy to S3
aws s3 cp $DIR/”$NOW”_”$db”.dump s3://$S3_PATH/”$NOW”_”$db”.dump –storage-class STANDARD_IA
echo “* Database:” $db “is archived at timestamp:” $NOW
# Delete local file
rm $DIR/”$NOW”_”$db”.dump
fi
# Log
#echo “* Database: ” $db “is archived at timestamp: ” $NOW
done

# Delere old files
echo “Following backups older than 7 days are deleted from s3://phi-postgresql-backup/postgresql01-pgbackup”;
$SCRIPT/s3_autodelete.sh $S3_PATH “7 days”

vi s3_autodelete.sh

#!/bin/bash

# Usage:
# ./s3_autodelete.sh bucket/path “7 days”

set -e

# Maximum date (will delete all files older than this date)
maxDate=`date +%s –date=”-$2″`

# Loop thru files
aws s3 ls s3://$1/ | while read -r line; do
# Get file creation date
createDate=`echo $line|awk {‘print $1″ “$2’}`
createDate=`date -d”$createDate” +%s`

if [[ $createDate -lt $maxDate ]]
then
# Get file name
fileName=`echo $line|awk {‘print $4’}`
if [[ $fileName != “” ]]
then
echo “* Delete $fileName”;
aws s3 rm s3://$1/$fileName
fi
fi
done;

Step 6:- Now schedule the script in cron pg_dump_s3.sh to run every day to take the latest backup to S3 and delete the old backup from S3.
sudo crontab -u postgres -e

0 23 * * * /var/lib/pgsql/9.6/data/scripts/pg_dump_s3.sh > /var/lib/pgsql/9.6/data/log/pg_dump_s3-`date +\%Y\%m\%d\%H\%M\%S` 2>&1

Step 7:- This step will explain the restoration part from S3. Only consideration made in this step is the DB name is a single word name which the script will fetch from the backup file name itself. If the DB name contain any special character then the italic line in the restoration script need to change accordingly to fetch the DB name.

The script will do the following:-

=> From the list of available backups in the S3 bucket, the script will identify the database name and the backup file.

=> Fetch the backup from S3 to local storage.

=> Drop old database and create new database with the same owner, tablespace and privileges.

=> Restore the dump to database.

cd $SCRIPTS

vi pg_restore_s3.sh
#!/bin/bash
set -e

# Usage:
# ./pg_restore_s3.sh backup_file_name

AWS_BUCKET=”phi-postgresql-backup/postgresql01-pgbackup”
DUMP_OBJECT=$1
DIR=”/postgresql_bkp/pgsql_backup_logical”

echo “Postgres restore from s3 – looking for dump in s3 at s3://phi-postgresql-backup/postgresql01-pgbackup”
if [ -n “${DUMP_OBJECT}” ]; then
objectSet=$(aws s3 ls s3://${AWS_BUCKET}/${DUMP_OBJECT}| awk ‘{print $4 }’)
if [ -z “$objectSet” ]; then
echo “Backup file not found in s3 bucket”
else
echo “Downloading dump from s3 – $object”
aws s3 cp s3://phi-postgresql-backup/postgresql01-pgbackup/${DUMP_OBJECT} ${DIR}/${DUMP_OBJECT}
dbName=$(echo $DUMP_OBJECT | awk -F _ ‘{print $2 }’| awk -F . ‘{print $1 }’)
echo “dropping old database $dbName”
dbPgOwner=$(echo “SELECT r.rolname FROM pg_catalog.pg_database d JOIN pg_catalog.pg_roles r ON d.datdba = r.oid JOIN pg_catalog.pg_tablespace t on d.dattablespace
= t.oid WHERE d.datname= ‘$dbName’;”| psql 2>&1)
dbOwner=$(echo $dbPgOwner | awk ‘{print $3 }’)
dbPgTblspc=$(echo “SELECT t.spcname FROM pg_catalog.pg_database d JOIN pg_catalog.pg_roles r ON d.datdba = r.oid JOIN pg_catalog.pg_tablespace t on d.dattablespace
= t.oid WHERE d.datname= ‘$dbName’;”| psql 2>&1)
dbTblspc=$(echo $dbPgTblspc | awk ‘{print $3 }’)
PRE_RESTORE_PSQL=”GRANT ALL PRIVILEGES ON DATABASE $dbName to $dbOwner; REVOKE connect ON DATABASE $dbName FROM PUBLIC;”
dropResult=$(echo “SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = ‘$dbName’; \
DROP DATABASE $dbName;” | psql 2>&1)
if echo $dropResult | grep “other session using the database” -> /dev/null; then
echo “RESTORE FAILED – another database session is preventing drop of database $dbName”
exit 1
fi
createResult=$(echo “CREATE DATABASE $dbName OWNER $dbOwner tablespace = $dbTblspc;” | psql 2>&1)
echo “postgres restore from s3 – filling target database with dump”
if [ -n “$PRE_RESTORE_PSQL” ]; then
echo “postgres restore from s3 – executing pre-restore psql”
printf %s “$PRE_RESTORE_PSQL” | psql
fi
pg_restore -d $dbName $DIR/$DUMP_OBJECT
fi
echo “Postgres restore from s3 – complete – $DUMP_OBJECT $dbName”
else
echo “Please pass the backup dump file name as script argument”
fi

Using the scripts above, you are storing the backup of multiple postgresql databases directly in S3 and also restoring particular dump file to postgresql without any manual effort.