Postgresql Architecture

 

Postgresql Instance-Cluster/Database/Schema

 

 

 

 

Cluster --> Instance --> set of databases

Above diagram shows that on a server we can have multiple Instances(Clusters) of postgresql running same version or different versions, but they listen on different ports.

FSx ontap CLI commands to check key metrics

 Login to FSx instance using management IP as "fsxadmin"

Enter diagnostic mode **caution while using this mode, should be done with consultation from NetApp Support

        In FSx CLI mode >>

        set d  (yes)

        command to check overall latency statistics
        qos statistics workload latency show

        command to check overall volume statistics
        statistics volume show

        command to check per volume latency details
        qos statistics volume latency show -volume vol01 -vserver svm_name

Setting up ssh keys for EC2 instance on AWS

 

 

 

 

create ssh key pair

ssh-keygen -f my-keypair

this creates 2 files my-keypair & my-keypair.pub in the current directory

use puttygen to convert my-keypair to my-keypair.ppk

we'll use my-keypair.pub on EC2 instance

end user/clients will use private key -- my-keypair.ppk

 

# create EC2 instance via Terraform

# creating test ec2 instance for using ssh keypair made with TF

resource "aws_instance" "mam_test1" {

  ami           = "ami-xxxxxxxx"

  instance_type = "t2.micro"

  security_groups =["sg-yyyy" ]

  associate_public_ip_address =false

  subnet_id = "subnet-zzzzzz"

  key_name = "my-keypair"

  tags = {

    Name = "my_keypair"

  }

}

resource "aws_key_pair" "may-keypair" {

  key_name = "my-keypair"

  public_key = "${file("my-keypair.pub")}"

 

}

output "mam_test1ip" {

  value=aws_instance.mam_test1.private_ip

}

Once the instance is created use the my-keypair.ppk to connect to EC2 instance.

FSxN - OnTAP on AWS limited by network bandwidth of EC2 Instance

In DBA world stakes are high for a business critical database demanding high levels of performance. As a robust & high performing storage is expected to meet the low latency requirements, OnTAP which has been delivering on storage front for decades,  extend their services on AWS Cloud as FSxN.

FSxN storage is being used as NFS mount on a Linux server (EC2 instance) hosting an Oracle database which is a part of Dataguard configuration. While a lag caused the standby database to become out of sync, once the underlying issue of extending mount was fixed, shipping & reading of archiving logs was painstakingly slow.

BlueXP & AWS Console showed no spike in key metrics of latency, IOPS or throughput, however the apply of archiving logs & copy of archive logs from remote primary database server to standby server was very slow.

Even though CPU, memory utilization was low, the copy of archive logs & apply of it was slow as the network throughput limit was reached for x1e.xlarge EC2 instance. To resolve this issue instance type was changed from x1e.xlarge to r6id.2xlarge, this has a throughput of 3125 mbps. Post instance type  change copy & apply of archive logs throughput increased & allowed to get standby in sync.

Reindexing to reduce bloat - postgresql

postgres=# create table demo1 as select * from generate_series(1,50000) as id;

SELECT 50000

postgres=# create index demo1_idx on demo1(id);

CREATE INDEX

postgres=# \dt

         List of relations

 Schema | Name  | Type  |  Owner

--------+-------+-------+----------

 public | demo1 | table | postgres

 public | ridx1 | table | postgres

(2 rows)

postgres=# \dt+

                                    List of relations

 Schema | Name  | Type  |  Owner   | Persistence | Access method |  Size   | Description

--------+-------+-------+----------+-------------+---------------+---------+-------------

 public | demo1 | table | postgres | permanent   | heap          | 1808 kB |

 public | ridx1 | table | postgres | permanent   | heap          | 43 MB   |

(2 rows)

postgres=# \di+

                                           List of relations

 Schema |    Name     | Type  |  Owner   | Table | Persistence | Access method |  Size   | Description

--------+-------------+-------+----------+-------+-------------+---------------+---------+-------------

 public | demo1_idx   | index | postgres | demo1 | permanent   | btree         | 1112 kB |

 public | ridx1_pk    | index | postgres | ridx1 | permanent   | btree         | 14 MB   |

 public | ridx1_x_idx | index | postgres | ridx1 | permanent   | btree         | 14 MB   |

(3 rows)

postgres=# delete from demo1 where id % 2 = 0;

DELETE 25000

postgres=# select pg_size_pretty(pg_relation_size('demo1')) as table_size, pg_size_pretty(pg_relation_size('demo1_idx')) as index_size, 100 - (pgstatindex('demo1_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size | bloat_ratio

------------+------------+-------------

 1776 kB    | 1112 kB    |       54.94

(1 row)

postgres=# reindex index demo1_idx;

REINDEX

postgres=# select pg_size_pretty(pg_relation_size('demo1')) as table_size, pg_size_pretty(pg_relation_size('demo1_idx')) as index_size, 100 - (pgstatindex('demo1_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size |    bloat_ratio

------------+------------+--------------------

 1776 kB    | 568 kB     | 10.819999999999993

(1 row)

pgstattuple - for tuple-level statistics & removing bloat by pg_repack

 pgstattuple module/extension provides various functions to obtain tuple-level statistics, however its not installed by default.

postgres=#  select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;

ERROR:  function pgstattuple(unknown) does not exist

LINE 1: ...(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattupl...

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

To create extension, you need to install 

[root@linuxpg1 ~]# yum install postgresql14-contrib

Loaded plugins: langpacks, ulninfo

ol7_UEKR5                                                | 3.0 kB     00:00

ol7_latest                                               | 3.6 kB     00:00

pgdg-common/7Server/x86_64/signature                     |  198 B     00:00

pgdg-common/7Server/x86_64/signature                     | 2.9 kB     00:00 !!!

pgdg11/7Server/x86_64/signature                          |  198 B     00:00

pgdg11/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg12/7Server/x86_64/signature                          |  198 B     00:00

pgdg12/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg13/7Server/x86_64/signature                          |  198 B     00:00

pgdg13/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg14/7Server/x86_64/signature                          |  198 B     00:00

pgdg14/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg15/7Server/x86_64/signature                          |  198 B     00:00

pgdg15/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

(1/6): pgdg13/7Server/x86_64/primary_db                    | 287 kB   00:03

(2/6): pgdg-common/7Server/x86_64/primary_db               | 178 kB   00:03

(3/6): pgdg15/7Server/x86_64/primary_db                    | 106 kB   00:00

(4/6): pgdg14/7Server/x86_64/primary_db                    | 193 kB   00:04

(5/6): pgdg12/7Server/x86_64/primary_db                    | 373 kB   00:05

(6/6): pgdg11/7Server/x86_64/primary_db                    | 487 kB   00:11

Resolving Dependencies

--> Running transaction check

---> Package postgresql14-contrib.x86_64 0:14.8-1PGDG.rhel7 will be installed

--> Processing Dependency: postgresql14-server(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64

--> Processing Dependency: postgresql14-libs(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64

--> Processing Dependency: postgresql14(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64

--> Processing Dependency: libpython3.6m.so.1.0()(64bit) for package: postgresql 14-contrib-14.8-1PGDG.rhel7.x86_64

--> Running transaction check

---> Package postgresql14.x86_64 0:14.7-1PGDG.rhel7 will be updated

---> Package postgresql14.x86_64 0:14.8-1PGDG.rhel7 will be an update

---> Package postgresql14-libs.x86_64 0:14.7-1PGDG.rhel7 will be updated

---> Package postgresql14-libs.x86_64 0:14.8-1PGDG.rhel7 will be an update

---> Package postgresql14-server.x86_64 0:14.7-1PGDG.rhel7 will be updated

---> Package postgresql14-server.x86_64 0:14.8-1PGDG.rhel7 will be an update

---> Package python3-libs.x86_64 0:3.6.8-18.0.5.el7 will be installed

--> Processing Dependency: python(abi) = 3.6 for package: python3-libs-3.6.8-18.0.5.el7.x86_64

--> Running transaction check

---> Package python3.x86_64 0:3.6.8-18.0.5.el7 will be installed

--> Processing Dependency: python3-pip for package: python3-3.6.8-18.0.5.el7.x86_64

--> Processing Dependency: python3-setuptools for package: python3-3.6.8-18.0.5.el7.x86_64

--> Running transaction check

---> Package python3-pip.noarch 0:9.0.3-8.0.3.el7 will be installed

---> Package python3-setuptools.noarch 0:39.2.0-10.0.1.el7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

================================================================================

Package                  Arch       Version               Repository      Size

================================================================================

Installing:

 postgresql14-contrib     x86_64     14.8-1PGDG.rhel7      pgdg14         688 k

Installing for dependencies:

 python3                  x86_64     3.6.8-18.0.5.el7      ol7_latest      70 k

 python3-libs             x86_64     3.6.8-18.0.5.el7      ol7_latest     6.9 M

 python3-pip              noarch     9.0.3-8.0.3.el7       ol7_latest     1.6 M

 python3-setuptools       noarch     39.2.0-10.0.1.el7     ol7_latest     628 k

Updating for dependencies:

 postgresql14             x86_64     14.8-1PGDG.rhel7      pgdg14         1.5 M

 postgresql14-libs        x86_64     14.8-1PGDG.rhel7      pgdg14         271 k

 postgresql14-server      x86_64     14.8-1PGDG.rhel7      pgdg14         5.4 M

Transaction Summary

================================================================================

Install  1 Package  (+4 Dependent packages)

Upgrade             ( 3 Dependent packages)

Total size: 17 M

Total download size: 9.9 M

Is this ok [y/d/N]: y

Downloading packages:

(1/5): python3-3.6.8-18.0.5.el7.x86_64.rpm                 |  70 kB   00:01

(2/5): python3-pip-9.0.3-8.0.3.el7.noarch.rpm              | 1.6 MB   00:00

(3/5): python3-setuptools-39.2.0-10.0.1.el7.noarch.rpm     | 628 kB   00:00

(4/5): python3-libs-3.6.8-18.0.5.el7.x86_64.rpm            | 6.9 MB   00:06

(5/5): postgresql14-contrib-14.8-1PGDG.rhel7.x86_64.rpm    | 688 kB   00:08

--------------------------------------------------------------------------------

Total                                              1.1 MB/s | 9.9 MB  00:08

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

  Installing : python3-libs-3.6.8-18.0.5.el7.x86_64                        1/11

  Installing : python3-pip-9.0.3-8.0.3.el7.noarch                          2/11

  Installing : python3-setuptools-39.2.0-10.0.1.el7.noarch                 3/11

  Installing : python3-3.6.8-18.0.5.el7.x86_64                             4/11

  Updating   : postgresql14-libs-14.8-1PGDG.rhel7.x86_64                   5/11

  Updating   : postgresql14-14.8-1PGDG.rhel7.x86_64                        6/11

  Updating   : postgresql14-server-14.8-1PGDG.rhel7.x86_64                 7/11

  Installing : postgresql14-contrib-14.8-1PGDG.rhel7.x86_64                8/11

  Cleanup    : postgresql14-server-14.7-1PGDG.rhel7.x86_64                 9/11

  Cleanup    : postgresql14-14.7-1PGDG.rhel7.x86_64                       10/11

  Cleanup    : postgresql14-libs-14.7-1PGDG.rhel7.x86_64                  11/11

  Verifying  : python3-3.6.8-18.0.5.el7.x86_64                             1/11

  Verifying  : postgresql14-libs-14.8-1PGDG.rhel7.x86_64                   2/11

  Verifying  : python3-pip-9.0.3-8.0.3.el7.noarch                          3/11

  Verifying  : python3-libs-3.6.8-18.0.5.el7.x86_64                        4/11

  Verifying  : postgresql14-14.8-1PGDG.rhel7.x86_64                        5/11

  Verifying  : python3-setuptools-39.2.0-10.0.1.el7.noarch                 6/11

  Verifying  : postgresql14-contrib-14.8-1PGDG.rhel7.x86_64                7/11

  Verifying  : postgresql14-server-14.8-1PGDG.rhel7.x86_64                 8/11

  Verifying  : postgresql14-server-14.7-1PGDG.rhel7.x86_64                 9/11

  Verifying  : postgresql14-14.7-1PGDG.rhel7.x86_64                       10/11

  Verifying  : postgresql14-libs-14.7-1PGDG.rhel7.x86_64                  11/11

Installed:

  postgresql14-contrib.x86_64 0:14.8-1PGDG.rhel7

Dependency Installed:

  python3.x86_64 0:3.6.8-18.0.5.el7

  python3-libs.x86_64 0:3.6.8-18.0.5.el7

  python3-pip.noarch 0:9.0.3-8.0.3.el7

  python3-setuptools.noarch 0:39.2.0-10.0.1.el7

Dependency Updated:

  postgresql14.x86_64 0:14.8-1PGDG.rhel7

  postgresql14-libs.x86_64 0:14.8-1PGDG.rhel7

  postgresql14-server.x86_64 0:14.8-1PGDG.rhel7

Complete!

[root@linuxpg1 ~]# su - postgres

Last login: Sat May 27 17:22:44 CDT 2023 on pts/1

ps-bash-4.2$ psql

psql (14.8)

Type "help" for help.

postgres=# create extension pgstattuple;

CREATE EXTENSION

postgres=#

Now you can use pgstattuple to query bloat (dead_tuple_percent)

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;

 table_size | index_size | dead_tuple_percent

------------+------------+--------------------

 65 MB      | 21 MB      |                  0

(1 row)

postgres=# delete from ridx1 where x % 3 = 0;

DELETE 333333

postgres=# analyze ridx1;

ANALYZE

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;

 table_size | index_size | dead_tuple_percent

------------+------------+--------------------

 65 MB      | 21 MB      |              29.78

(1 row)

Index bloat

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size | bloat_ratio

------------+------------+-------------

 65 MB      | 21 MB      |       39.86

(1 row)

 postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1

INFO: Dry run enabled, not executing repack

ERROR: pg_repack failed with error: pg_repack 1.4.8 is not installed in the database

-bash-4.2$ psql

psql (14.8)

Type "help" for help.

postgres=# select * from pg_extension;

  oid  |   extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition

-------+-------------+----------+--------------+----------------+------------+-----------+--------------

 14473 | plpgsql     |       10 |           11 | f              | 1.0        |           |

 33437 | pgstattuple |       10 |         2200 | t              | 1.5        |           |

(2 rows)

postgres=# create extension pg_repack;

CREATE EXTENSION

postgres=# select * from pg_extension;

  oid  |   extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition

-------+-------------+----------+--------------+----------------+------------+-----------+--------------

 14473 | plpgsql     |       10 |           11 | f              | 1.0        |           |

 33437 | pgstattuple |       10 |         2200 | t              | 1.5        |           |

 33448 | pg_repack   |       10 |         2200 | f              | 1.4.8      |           |

(3 rows)

postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1

INFO: Dry run enabled, not executing repack

WARNING: relation "public.ridx1" must have a primary key or not-null unique keys

-bash-4.2$ psql

psql (14.8)

Type "help" for help.

postgres=# alter table ridx1 add constraint ridx1_pk primary key (x);

ALTER TABLE

postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1

INFO: Dry run enabled, not executing repack

INFO: repacking table "public.ridx1"

-bash-4.2$ psql

psql (14.8)

Type "help" for help.

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size | bloat_ratio

------------+------------+-------------

 65 MB      | 21 MB      |       39.86

(1 row)

postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack -d postgres --table ridx1

INFO: repacking table "public.ridx1"

-bash-4.2$ psql

psql (14.8)

Type "help" for help.

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size |    bloat_ratio

------------+------------+-------------------

 43 MB      | 14 MB      | 9.939999999999998

(1 row)

pg_repack address bloat on table in postgresql14

pg_repack can be used as online operation to address bloat on a table in postgresql14.

Advantages of using pg_repack

• Releases storage from a table to disk/FS
• Rebuild a table - reduces I/O
• Release storage piled due to dead tuples as bloat & not cleared by Auto vacuum

Install pg_repack on server
 [root@linuxpg1 bin]# yum install pg_repack_14
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package pg_repack_14.x86_64 0:1.4.8-1.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===========================================================================================================================================
 Package                            Arch                         Version                                Repository                    Size
===========================================================================================================================================
Installing:
 pg_repack_14                       x86_64                       1.4.8-1.rhel7                          pgdg14                       127 k
Transaction Summary
===========================================================================================================================================
Install  1 Package
Total download size: 127 k
Installed size: 311 k
Is this ok [y/d/N]: y
Downloading packages:
pg_repack_14-1.4.8-1.rhel7.x86_64.rpm                                                                               | 127 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pg_repack_14-1.4.8-1.rhel7.x86_64                                                                                       1/1
  Verifying  : pg_repack_14-1.4.8-1.rhel7.x86_64                                                                                       1/1
Installed:
  pg_repack_14.x86_64 0:1.4.8-1.rhel7
Complete!

[root@linuxpg1 bin]# pwd
/usr/pgsql-14/bin

[root@linuxpg1 bin]# ls
clusterdb   initdb             pg_config       pg_isready     pg_rewind        pg_waldump                  psql
createdb    pg_archivecleanup  pg_controldata  pg_receivewal  pg_test_fsync    postgres                    reindexdb
createuser  pg_basebackup      pg_ctl          pg_repack      pg_test_timing   postgresql-14-check-db-dir  vacuumdb
dropdb      pgbench            pg_dump         pg_resetwal    pg_upgrade       postgresql-14-setup
dropuser    pg_checksums       pg_dumpall      pg_restore     pg_verifybackup  postmaster
[root@linuxpg1 bin]#

switch to postgres user

-bash-4.2$ psql

psql (14.7)

Type "help" for help.

postgres=# set shared_preload_libraries = 'pg_repack';

ERROR:  parameter "shared_preload_libraries" cannot be changed without restarting the server

restart postgresql

[root@linuxpg1 bin]# systemctl restart postgresql-14

[root@linuxpg1 bin]# systemctl status postgresql-14

● postgresql-14.service - PostgreSQL 14 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)

   Active: active (running) since Mon 2023-05-15 00:37:20 CDT; 19s ago

     Docs: https://www.postgresql.org/docs/14/static/

  Process: 7950 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)

 Main PID: 7959 (postmaster)

    Tasks: 8

   CGroup: /system.slice/postgresql-14.service

           ├─7959 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/

           ├─7962 postgres: logger

           ├─7964 postgres: checkpointer

           ├─7965 postgres: background writer

           ├─7966 postgres: walwriter

           ├─7967 postgres: autovacuum launcher

           ├─7968 postgres: stats collector

           └─7969 postgres: logical replication launcher

May 15 00:37:20 linuxpg1 systemd[1]: Starting PostgreSQL 14 database server...

May 15 00:37:20 linuxpg1 postmaster[7959]: 2023-05-15 00:37:20.547 CDT [7959] LOG:  redirecting log output to logging collector process

May 15 00:37:20 linuxpg1 postmaster[7959]: 2023-05-15 00:37:20.547 CDT [7959] HINT:  Future log output will appear in directory "log".

May 15 00:37:20 linuxpg1 systemd[1]: Started PostgreSQL 14 database server.

[root@linuxpg1 bin]#

-bash-4.2$ psql

psql (14.7)

Type "help" for help.

postgres=# \l

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 db1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+

           |          |          |             |             | app_ro=c/postgres

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

(4 rows)

connect to postgres db

postgres=# \c postgres

create extension pack in postgres database

postgres=# create extension pg_repack;

CREATE EXTENSION

postgres=#

check location of pg_repack & add it to PATH varaible

-bash-4.2$ which pg_repack

/usr/pgsql-14/bin/pg_repack

-bash-4.2$ echo $PATH

/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/usr/pgsql-14/bin

-bash-4.2$

Execute pg_repack

-bash-4.2$ pg_repack --dry-run -d postgres --table tab1

INFO: Dry run enabled, not executing repack

WARNING: relation "public.tab1" must have a primary key or not-null unique keys

-bash-4.2$

Add primary key

ALTER TABLE public.tab1 ADD CONSTRAINT tab1_pk PRIMARY KEY (a1);

Run pg_repack dry 

-bash-4.2$ pg_repack --dry-run -d postgres --table tab1

INFO: Dry run enabled, not executing repack

INFO: repacking table "public.tab1"

-bash-4.2$

Run pg_repack to address bloat

-bash-4.2$ pg_repack  -d postgres --table tab1

INFO: repacking table "public.tab1"

-bash-4.2$

PostgreSQL DBA helpful stuff!

PostgreSQL commands

# to get help on psql 

psql --help

# Examples of psql connection to DB using various switches/flags

psql postgres postgres -h 192.168.0.25 -p 5432 (-h flag to specify hostname)
psql -U postgres -h 192.168.0.25 -p 5432 -W  (-U flag to specify db user)

psql -U postgres -d db1 -h 192.168.0.25 -p 5432 (-d flag to specify db)

#to know current user

select current_user;

#to get current date

select current_date;

#to know user details

select * from pg_user;

#to know about databases

select * from pg_database;

#to know about tables

select * from pg_class;

#to know about roles

select * from pg_roles;

 

# to query parameters

SELECT name as "Parameter name", setting as value, short_desc FROM pg_settings WHERE name LIKE '%ssss%'; 

#to know relative file path

select pg_relation_filepath('course');

#get details of user connections

select state, count(*) from pg_stat_activity group by state;

select * from pg_stat_activity where state='idle';

SELECT pg_terminate_backend(pid);

#shortcut commands on psql

postgres=# \l -- list db

postgres=#\c dbname -- to connect to a particular db

# to know dead tuples in a table

postgres=#select relname, n_dead_tup from pg_stat_user_tables;

#to vacuum manually

postgres=# vacuum (verbose, analyze);

#to know size of tables

select relname as "tableName", pg_size_pretty(pg_table_size(pgc.oid)) as "spaceused"

from pg_class as pgc left join pg_namespace as pgns on (pgns.oid = pgc.relnamespace)

where nspname not in ('pg_catalog', 'information_schema') and nspname !='pg_toast' and relkind in ('r')

order by pg_table_size(pgc.oid) desc;

#Query to check is SSL is being used by sessions

SELECT datname as "Database name", usename as "User name", ssl, client_addr, application_name, backend_type
FROM pg_stat_ssl JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid ORDER BY ssl;

#Indexes Info

postgres=# \di

postgres=# \di+

postgres=# \dis

# to get index, table its on & columns its created on

select row_number() over (order by c.relname), c.relname as index, t.relname as table, array_to_string(array_agg(a.attname), ', ') as column_names from pg_class c join pg_index i on c.oid = i.indexrelid and c.relkind='i' and c.relname not like 'pg_%' join pg_class t on t.oid= i.indrelid left join pg_attribute a on a.attrelid = t.oid and a.attnum = ANY(i.indkey) group by t.relname, c.relname order by c.relname;

#when last auto vacuum happened

select relname, last_vacuum, last_autovacuum from pg_stat_user_tables;

#to vacuum table

vacuum full tab1; -- tab1 is table name

#to know details about Archiving

postgres=# select name, setting, unit from pg_settings where name in

('wal_level', 'wal_keep_size', 'wal_keep_segments', 'wal_segment_size', 'archive_mode', 'archive_command', 'archive_timeout');

#to Check number of connections, super user connections, max connections

select * from (select count(*) used from pg_stat_activity) q1, (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) q2, (select setting::int max_conn from pg_settings where name=$$max_connections$$) q3;

#to take online backups

#can take backups as postgres user or create user with replication role

pg_basebackup -U username -D /location/wheretoplacebackups

pg_basebackup -v -U username -D /location/wheretoplacebackups

pg_basebackup -U username -D /location/wheretoplacebackups -h localhost

pg_basebackup -U username -D /location/wheretoplacebackups -Ft -z -P (-Ft - format tar file, z for compression P for progress)

#for upgrade

pg_upgrade

--old-datadir '/pg/9.x/data' --new-datadir '/pg/10.x/data'

--old-bindir '/pg/9.x/bin' --new-bindir '/pg/10.x/bin'

pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin - B newCluster/bin

or you can export

$export PGDATAOLD = oldCluster/data

$export PGDATANEW = newCluster/data

$export PGBINOLD = oldCluster/bin

$export PGNEWOLD = newCluster/bin

$pg_upgrade

#to create db user

create user user1;

# to know the process tree for postgresql processes

pstree -p 7080 (where 7080 is postmaster pid)

PG Background Process

logger -- writes messages to error logs

checkpointer -- flushes dirty buffer to files (periodically)

background writer -- writes dirty buffer to files

wal writer -- writes wal buffer to wal file

autovaccum launcher -- performs vacuum operations

archiver-- for archiving

stats collector -- DBMS usage stats (session info, table usage stats)..etc.

PG Memory pools

shared memory -- db caching & transaction log caching

shared buffer -- frequently used blocks cached

wal buffer -- changes to db

postmaster process -- first to start, initialize memory, run background process, helps in recovery, creates background process for a client connection

maintenance_work_mem - for vacuum & create index, joins

temp_buffers -- temp tables area