PostgreSQL

Transaction:
  1. Transaction are expected to be Atomic, Consistent, Isolated and Durable. A transaction is a group of SQL commands whose results will be made visible to the rest of the system as a unit when the transaction commits or not at all, if the transaction aborts.
  2. Postgres does not support distributed transactions, so all commands of a transaction are executed by one backend.


ACID:

Atomicity - Results of a transaction is seen entirely or not at all within other transaction.

Consistency - The consistency property ensures that any transaction will bring the database from one valid state to another. One committed state to another committed state.

Isolation - Transactions are not affected by the behaviour of concurrently running transactions.
Durability - Once a transaction commits, its result will not be lost regardless of any failure.


Isolation Level:



Read Committed - Default Isolation level in PostgreSQL. When a transaction uses this isolation level, user can see only committed data. But within a transaction, there can be two different result of select query. 1st output can be when user did not commit in different transaction. 2nd output can be when user committed data in different transaction.



Repeatable Read - The Repeatable Read isolation level only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions.



Serializable - This level emulates serial transaction execution, as if transactions had been executed one after another, serially, rather than concurrently.



Here, isolation level works exactly the same as Repeatable Read except that it monitors for conditions which could make execution of a concurrent set of serializable transactions behave in a manner inconsistent with all possible serial (one at a time) executions of those transactions.



A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.



As if all transactions in the system were executed serially, one after the other. The DBMS can execute two or more transactions at the same time only if the illusion of serial execution can be maintained.


Read Uncommitted - PostgreSQL is immune to dirty reads, requesting a Read Uncommitted transaction isolation level provides read committed instead.


MVCC:
  1. Postgres uses MVCC so that readers don't block writers and writers don't block readers. Transactions proceed concurrently where there is no contention.
  2. Writer only block each other when updating the same row.
  3. Each transaction sees a snapshot(database version) as of its start time, no matter what other transaction are doing while it runs
Facts about PostgreSQL:

  1. The world's most advanced open source database. Have source code available.
  2. It is written in C language.
  3. We can modify our own PostgreSQL code and sell it to customers.
  4. Designed for extensibility and customization
  5. ANSI/ISO compliant SQL Support
  6. Actively developed for more than years
  7. Postgres - (1986 - 1993)
  8. Postgres95 - ( 1994 - 1995)
  9. PostgreSQL - ( 1996 - present)


ARCHITECTURE OF POSTGRSQL:


  1. PostgreSQL uses process, not thread.
  2. Postmaster process acts as a supervisor.
  3. Several background process if killed like bgwriter, postmaster will try to restart them, if it can not restart the background process, postmaster will die.
  4. One backend process per user session( postmaster listens for new connection)
  5. Max_connection includes postmaster, bgwriter and user process(client process).
  6. When server starts, it starts postmaster and when postmaster starts it starts the utility process(bgwriter, wal writer, log writer, archiver, stats collector, auto vacuum, checkpointer, syslogger)
  1. Data files, WAL segments and archived wall are in binary format where as error log file are in readable format.
  2. Stats collector - Collect usage statistics  by relation and block, how many users, insert, update, delete in a table inserted in data file, data files contain dictionary. It does not do rows sampling by default.
  3. Checkpointer - Enables consistency between wal segments and data files. Transaction logs are written continuously to wal segments but data may not be written to data files by bgwriter utility process. Checkpointer ensures consistency between WAL segments and data files. Every 5 mins checkpointer says bgwriter, how much work have you done, ok you have already written 100 pages, now let me take over and do the rest of work to write the dirty buffers to datafiles. It is from 9.5. It have more priority than bgwriter. It does same work as bgwriter do but in a flash. Default is 5 Mins.
  4. BGWRITER:Continuously writes dirty data blocks to data files. By default:200ms. Parameter is bgwriter_delay.
  5. Shared_buffer: Read, insert, update are done here. Read and write happens here.
  6. Auto-Vacuum - It marks obsolete rows as reusable by the same table. Free Space Mapping(FSM) is created when Auto -Vacuum runs. It contains link to all available free space within the table. Autovacuum Launcher - starts autovacuum workers as needed. Autovacuum workers - recover free space for reuse.
  7. WAL Writer - Flushes Write ahead log to disk. It writes only committed transactions.
  8. Logging Collector - Routes log messages to syslog, event log(windows) or log files.
  9. Archiver - Archives WAL files.
  10. Mandatory Utility Process - bgwriter, checkpointer, wal writer, stats collector.
  11. Auto vacuum we can turn it off.
  12. Postmaster is master process called postgres. Receives client connection request. 5432/5444(TCP Port) are default port community/PPAS respectively. TCP - Transmission Control Protocol - end point of communication in an OS.
  13. UNIX Domain or IPC socket is a data communication end point for exchanging data between processes executing on the same host OS. Like named pipes unix domain sockets support, transmission of an reliable stream of bytes. It is an standard component of POSIX OS.
  14. Backend Spawning - Master process postgres spawns a new server process for each connection request detected. Communication is done using semaphore and shared memory. Authentication of IP, user and password and authorization - Verify permission. Postmaster initiates the new user process for each request.
  15. Work mem is like PGA used for sorting per user process
  16. Buffer cache reduces OS reads. Read the block once, then examine it many times in cache.
  17. Pg_pre_warm - we can pre-load table at server starts. Either in OS cache or Shared buffer. It is from 9.4 version.
  18. bgwriter writes to disk when there is a checkpoint and new blocks are needed(LRU). Bgwriter makes sure shared buffer is clean. If requirement is of 100 MB, it clears up 200 MB.
  19. WAL - At time of commit or buffer is full, all transaction are written to Transaction logs. Backend writes data to WAL buffers.
  20. Group Commit - Commit group of transaction in a batch. It is from 9.2 version. It will wait 10s and commit all transaction in a batch. It will lessen the I/O.
  21. Commit and checkpoint - Before commit - uncommitted updates are in memory. After commit - committed updates written from shared memory(WAL Buffer) to wal segment. After checkpoint - modified data pages are written from shared memory(Shared buffer) to data files.
  22. What happens if you fire a select query -
          Parse - Check Syntax, call traffic cop, identify query type, command processor if needed,
           break query into tokens.
           Optimize - Planner generates plan, uses database statistics, query cost calculation,
           choose best plan.
           Execute - Execute query based on query plan
  1. Cluster - directory, DB - directory, Table - File segment.
  2. 1 GB max will be file size for a given table while max size of the table will be 32TB.
  3. Each table is different file inside DB directory.Max of 1GB file, then next file will get created.
  4. 1 cell value more than 1 GB can not be stored. But row chaining is possible.


How connection are established:

  1. Postgres uses simple process per user model. It is also called client/server model.
  2. One client process is connected to one server process named postgres.
  3. The master process is called postgres and it listens to specified TCP/IP port for incoming connection.
  4. The server tasks communicate to each other using semaphore and shared memory to ensure data integrity throughout concurrent data access.
  5. Query is transmitted to backend server using plain text.
  6. The server parses the query, creates an execution plan, executes the plan and returns the retrieved rows to the client transmitting them over established connection.
Create USER, ROLES, GRANTS, PRIVILEGES and AUDIT:
  1. How do we create a user?
- Create user admin with password ‘admin’ valid until ‘2005-01-01’;
- Role is Alias for Create user.
- Create group is alias for create role.
- Create role role_name with LOGIN.
- \du
- Alter role role_name with nologin;
- GRANT UPDATE ON table_name TO role_name;
- GRANT ALL ON table_name TO role_name;
- GRANT INSERT ON table_name TO role_name;
- \z to check access privileges
- REVOKE INSERT ON table_nameFROM user_name;
- CREATE ROLE temporary_users;
- GRANT temporary_users TO user_name;

- GRANT temporary_users TO test_user;



PostgreSQL BACKUPS:
  1. What are the types of backup?
           A. SQL Dump
           B. Cluster Dump
           C. File system level backup
           D. Offline copy Backup
           E. Continuous Archiving
           F. Pg_basebackup
           G. PITR
  1. What is SQL Dump Backup?
           It generates a text file backup with SQL commands. PostgreSQL provides the utility program pg_dump for this purpose. Pg_dump does not block readers or writers. Pg_dumps does not operates with special permissions but superuser is suggested as it will have all the permissions of tables and databases. Dumps created by pg_dump is internally consistent. It represents a snapshot of the snapshots of the database as the time pg_dump begins running.

  1. What are the different options of pg_dump to take backup?
-a - Data only. Do not dump the data definition.
--column-inserts - Dumps in insert format rather copy which is default
--inserts - Dumps in insert format instead copy
--no-tablespace - Without tablespaces
-s - Data definitions(schema only). Do not dump the data
-n <schema_name> - Dump the specified schema only
-n <schema_name> -n <schema_name> - Dump 2 schema together
-t <table_name> - Dump specified table only
-t <table_name> -t <table_name> - Dump 2 tables together
-T <table_name> - Exclude the table from backup dump
-f <path/filename.sql> send dump to specified file
-Fp - Dump in plain text SQL script (Default)
-Ft - Dump in tar format
-Fc - Dump in compressed, custom format
-z - Used with -Fc to specify the level of compression. Values are from 1 to 9
-C - It will include database name in the dump file. Default it won't have DB name
-v - verbose
-o - use - OID preserver the OID of tables
-O - without ownership.
-V - display the version
-X - Dump without privileges. Without grants and revoke.

  1. How do we take backup ?
pg_dump -U postgres -f db_name.sql db_name

  1. How do we take compressed and tar backup?
pg_dump -U postgres -Fc -f db_name.sql db_name
pg_dump -U postgres  -Ft -f db_name.sql db_name

  1. How to take table backup?
pg_dump -U postgres -t table_name -f table_name_db_name.sql db_name

  1. How to take only data as backup and restore?
pg_dump --data-only --table=nps_scores ps_admin_juliet_dev > nps_scores_table.sql
Get inside the DB - \c db_name
Begin;
Alter table nps_scores_table disable trigger all;
truncate table table_name
\i /home/rsaha/table_name.sql
Alter table table_name enable trigger all;
commit;

  1. How will we backup Larger DB?
If OS have maximum file size limit, it causes problems when creating large pg_dump output file. We can dump in such scenario using gzip and split option of linux.
Pg_dump db_name| gzip >filename.gz
Pg_dump fb_name | split -b 1m - filename     -- 1MB split
We can use snapmirror/Snapshot to Backup Larger DB(OS Level)
We can backup using pg_basebackup everyday and backup all WAL in the full day

  1. How we will take compressed & Tar Dump?
pg_dump -U postgres -Fc -f db_name.sql db_name
pg_dump -U postgres -Ft -f db_name.sql db_name

  1. How we will take backup of Roles?
pg_dumpall -U postgres -g -f globals.sql

  1. How we will take cluster Backup?
Pg_dumpall is used to dump an entire database cluster in plain text SQL format.  
Dumps global object - user, groups and associated permissions.
pg_dumpall -U postgres -f cluster_backup.sql

  1. How do we take filesystem level backup(Offline Backup)?
We directly copy the data directory to backup directory. PostgreSQL service should be stopped and backup copy should be done.
psql> show data_directory;
EX: tar -cf backup.tar /usr/local/pgsql/data
Complete cluster is taken and complete cluster is restored. It works for production servers also. Tablespace must be also taken backup. psql>\db - list of tablespace . It will show the location also. Pg_xlog also must be taken backup if pg_xlog is not inside data directory if we do not stop the server and take the copy, data will get restored and start also but there is possible of table crash or table missing.


           
PostgreSQL RESTORATION & RECOVERY:
  1. What is meant by restoring from archive backup?
It means backup restored from compressed or tar backup.

  1. How will we restore a plain text Dump?
Create a Empty DB -
psql> CREATE DATABASE db_name WITH TEMPLATE = template0 OWNER = postgres  ENCODING = 'SQL_ASCII';  or
shell> createdb db_name
shell> dropdb db_name - For information
From Backup we will restore the DB -
psql -U postgres db_name  -f db_name.sql
                      
Pg_restore options:
-d <db_name> - Connects to the specified DB. Also restores to this DB if -C option is notm mentioned
-C - Creates the DB named in the dump file & restore directly into it
-a - restore the data only
-s - restores the data definition only. (schema only)
-n <schema_name> - restores only object from specified schema.
-t <table_name> - restores specified table
--disable-triggers - to disable trigger and fasten the restore process
-j - number of jobs using for restoration parallely.
-v - verbose option
  1. How to restore from tar backup?
pg_restore -Ft -U postgres -j 24 -d db_name db_name.tar

  1. How to restore from compressed backup?
pg_restore -Fc -U postgres -j 24 -d db_name db_name.compressed

  1. How do we restore entire cluster from pg_dumpall ?
Psql -U postgres -f pg_dumpall.sql

  1. How do we restore only global objects i.e permission, roles?
Psql -U postgres -f pg_dumpall_roles.sql

  1. How do we restore file system level backup?
Stop the server
Remove the old directory
copy the backup directory
Cp -rp backup_data /dat_directory    --r- recursive -p-preserve privilegeStart the server
CONTINUOUS ARCHIVING:
  1. PostgreSQL maintains  WAL files for all transaction in pg_xlog directory
  2. PostgreSQL automatically maintains the WAL Logs which are full and switched.
  3. Continuous archiving can be set up to keep a copy of switched WAL logs which can be later used for recovery.
  4. It also enables online file system backup of a database cluster
  5. Parameters
  1. wal_level=archive
  2. archive_mode=on
  3. archive_command=’cp %p /wal_logs/%f’
  1. Wal_logs should be own by postgres user
  2. %p and %f are name of the xlog.
  3. Select pg_switch_xlog(); Manually it will force checkpoint and switch log and check archiving is working or not.
PG_RECEIVEXLOG:
  1. Pg_receivexlog streams transaction log from a running postgresql cluster.
  2. This tool uses streaming replication protocol to stream transaction logs to a local directory.
  3. These files can be used for PITR.
  4. Transaction logs are streamed in real time thus pg_receivelog can be used instead of archive command.
  5. EX: pg_receivelog -h localhost -D /usr/local/wal_archive
  6. It can be used as an alternate of archiving or for streaming replication.
  7. Max_wal_sender=1 . At Least 1 and create entry in pg_hba.conf for the IP as replication user, Then it will work
  8. It uses replication protocol
ONLINE FILE SYSTEM BACKUP(BASE BACKUP):
  1. It can be done in two ways. Low level API and High level API. It should have archiving on.


LOW LEVEL API:(Old/ Traditional Technique)
  1. psql> select pg_start_backup(‘backup’);
  2. Copy or rsync the data directory. It will contain the backup label. Backup label contains checkpoint number
  3. Copy tablespace directory.
  4. psql> select pg_stop_backup();
  5. Select pg_is_in_backup();    to check if it is in backup mode.
HIGH LEVEL API:( pg_basebackup):
  1. Pg_basebackup takes backup of the full cluster.
  2. The backup can be used for replication or PITR.
  3. Pg_base_backup makes a binary copy of the database cluster files.
  4. System is automatically put in and out of backup mode.
  5. 9.1 onwards
  6. Option using pg_basebackup
    1. -D Directory of location of backup
    2. -Fp -Ft Plain or tar backup format
    3. -X <f or s> include required transaction log file
    4. -z enable gzip compression for files
    5. -Z compression level
    6. -P Progress reporting
    7. -h host on which cluster is running
    8. -p cluster port
  7. Pg_basebackup -h localhost -D /usr/local/pgsql/backup
  8. We need to modify pg_hba.conf and postgresql.conf files
    1. Host replication postgres [ipv4 address of client]/32 trust
    2. Host replication postgres 127.0.0.1/32 trust
    3. archive_command=’cp %p /usr/local/wal_archive/%f’
    4. archive_mode =on
    5. max_wal_sender=3
    6. wal_keep_segments=128
    7. wal_level=archive
    8. Suppose we are taking pg_basebackup from server B(content of A). Then in A we need to add IP of server B in pg_hba.conf
  9. /opt/PostgresPlus/9.3AS/bin/pg_basebackup -h 192.168.XX.XX(master IP) -U replicator -D /opt/PostgresPlus/9.3AS/data --xlog-method=stream -P --checkpoint=fast > /tmp/backup.out 2>&1 &
  10. Backup Level
Backup Lebel.PNG
  1. Backup method is pg_start_backup if we use pg_start backup instead basebackup.
           
Point-In-Time Recovery:(PITR)
  1. PITR is the ability to restore a database cluster up to the present or to a specified point in time in the past.
  2. Uses a full database cluster backup and the WAL found in pg_xlog directory
  3. Archiving must be enabled.
Prerequisite:
  1. Archiving must be enabled
  2. Make changes in postgresql.conf file
    1. Wal_level = hot_standby/archive
    2. archive_mode=on
    3. archive_command=’cp %p /var/lib/pgsql/pg_log_archive/%f’
  3. Create /var/lib/pgsql/pg_log_archive with postgres ownership to directory
  4. Take base backup with high level API
Steps:
  1. Stop the server, if it is running
  2. If we have enough space, keep a backup of data directory and transaction logs
  3. Remove all directories and files from the data directory
  4. Restore the data directory from backup
  5. Verify the ownership. It should be postgres
  6. Remove any files inside pg_xlog
  7. Restore the pg_xlog/* from the data directory of crashed cluster
  8. Create a recovery.conf file inside data directory of cluster
  9. Start the server
  10. Upon completion of the recovery process, the server will rename recovery.conf to recovery.done

  Installation Script:(Source)

Just copy the below lines and create a file. Your linux system will be ready with custom PostgreSQL installation. You can modify the script based on your need. You can consider the below lines as a framework. Cheers !!

Environment - Amazon Linux


#!/bin/bash
set -x

sudo su - <<EOF

/usr/bin/yum -y install gcc
/usr/bin/yum -y install readline-devel
/usr/bin/yum -y install zlib-devel


echo "Adding User for Postgres server"

/usr/sbin/useradd -d /home/postgres -m postgres
read -s -p "Enter password for postgres: " password
echo $password |passwd --stdin postgres
chage postgres -M -1


#/usr/sbin/useradd -d /home/postgres -m postgres
#passwd postgres
#read -p "New password:" postgres
#read -p "Retype new password " postgres

/bin/tar -xzf postgresql-10.1.tar.gz
cd postgresql-10.1
/root/postgresql-10.1/configure --prefix=/usr/local/pgsql10/
make
make install

/bin/mkdir /usr/local/pgsql10/data
/bin/chown postgres:postgres -R /usr/local/pgsql10

EOF

sudo su - postgres <<EOF

cd /usr/local/pgsql10/bin
/usr/local/pgsql10/bin/initdb -D /usr/local/pgsql10/data
/usr/local/pgsql10/bin/pg_ctl -D /usr/local/pgsql10/data start

EOF

sudo su - <<EOF

/bin/ln -s /usr/local/pgsql10/bin/psql /usr/bin

EOF

sudo su - postgres <<EOF

/usr/bin/perl -i -p -e "s/\#listen_addresses = 'localhost'/listen_addresses = '*'/;" /usr/local/pgsql10/data/postgresql.conf

/usr/bin/perl -i -p -e "s/shared_buffers = 128MB/shared_buffers = 2GB/;" /usr/local/pgsql10/data/postgresql.conf


/usr/bin/perl -i -p -e "s/\#port = 5432/port = 5433/;" /usr/local/pgsql10/data/postgresql.conf

/usr/local/pgsql10/bin/pg_ctl -D /usr/local/pgsql10/data restart

EOF

Install Pgbouncer in Linux:


 Environment - Amazon Linux/RHEL

Pre-Requiste:

Should have PG installed.( Please refer - http://postgresql-open-source.blogspot.in/2017/12/postgresql-installation-script-source.html)

Installation Steps- 

1. Download and install libevent from 'http://libevent.org/'(latest release) which is necessary for pgbouncer

sudo su -
yum install wget
wget https://github.com/libevent/libevent/releases/download/release-2.1.8-stable/libevent-2.1.8-stable.tar.gz
tar -xzf libevent-2.1.8-stable.tar.gz 
cd libevent-2.1.8-stable
yum install gcc
./configure
make 
make install

2. Download pgbouncer from this link 'http://pgfoundry.org/frs/?group_id=1000258'

wget http://pgfoundry.org/frs/download.php/3393/pgbouncer-1.5.4.tar.gz
tar -xzf pgbouncer-1.5.4.tar.gz
cd pgbouncer-1.5.4
./configure --prefix=/usr/local
make
make install

3. Enter the lib file location into '/etc/ld.so.conf.d/libevent-i386.conf' and save the file and load

vi /etc/ld.so.conf.d/libevent-i386.conf and enter /usr/local/lib/ and save the file and load
ldconfig

4. Create log directory

mkdir -p /var/log/pgbouncer
mkdir -p /var/run/pgbouncer

5. Change the ownership to 'postgres' for both the directory
chown postgres:postgres /var/log/pgbouncer
chown postgres:postgres /var/run/pgbouncer


6. Change the ownership to 'postgres' for pgbouncer binary

cd /usr/local/bin/
chown -R postgres:postgres pgbouncer


7. copy all the roles and passoword of postgres 

[postgres@ip-172-31-74-77 pgsql10]$ psql

postgres=# \o /usr/local/pgsql10/pgbouncer.auth
postgres=# select '"'||rolname||'"'||' "'||rolpassword||'"' from pg_authid;
postgres=# \o
postgres=# \! cat /usr/local/pgsql10/pgbouncer.auth
                     ?column?                     
--------------------------------------------------
 "new_user" "md500084d8780bcdf83b7345e04c09e0584"
 "postgres" "md532e12f215ba27cb750c9e093ce4b5127"
(7 rows)


8. Edit the 'pgbouncer.auth' file to have only 'role' and 'password'

vi /usr/local/pgsql10/pgbouncer.auth

"new_user" "md500084d8780bcdf83b7345e04c09e0584"
"postgres" "md532e12f215ba27cb750c9e093ce4b5127"


9. Edit the Configuration File 

vi /etc/pgbouncer.ini
[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /tmp
#auth_type = any
auth_type = trust
auth_file = /usr/local/pgsql10/pgbouncer.auth
admin_users = postgres
stats_users = postgres
pool_mode = transaction
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10
max_client_conn = 1000
default_pool_size = 20
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1


10. Start pgbouncer

[postgres@ip-172-31-74-77 bin]$ /usr/local/bin/pgbouncer -d /etc/pgbouncer.ini
2017-12-15 04:15:34.821 4794 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 1000, max fds possible: 1010


11. Check if pgbouncer is installed successfully -

[postgres@ip-172-31-74-77 pgbouncer]$ psql -p 6432 -U postgres -d pgbouncer
psql (10.1, server 1.5.4/bouncer)
Type "help" for help.

pgbouncer=# show help;
NOTICE:  Console usage
DETAIL:  
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SET key = arg
RELOAD
PAUSE [<db>]
RESUME [<db>]
KILL <db>
SUSPEND
SHUTDOWN
SHOW
pgbouncer=# 



Find Out Blocked Queries In PostgreSQL 9.6 


We can find the blocked queries using the below queries from PostgreSQL version 9.6 onwards.


select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

Also, we can check if queries are blocked from OS level using -

[rahul@linux] ps -ef|grep wait


PostgreSQL - Find out blocked queries in PostgreSQL version 9.5 and earlier
Below is the query that can be used to find out blocked queries in PostgreSQL version 9.6 onwards -

 SELECT blockingl.relation::regclass,
  blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(blockingl.relation=blockedl.relation
  AND blockingl.locktype=blockedl.locktype AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted;

 Once we figure out the blocked query, we can kill the blocked query using the PID and below command -

 postgres=# select pg_terminate_backend(3014);

 We can also figure out if queries are waiting from OS using the below command -
[rahul@linux] ps -ef | grep wait 

Installation Script PostgreSQL with Source Code Change(Ubuntu):



 #!/bin/bash
aptitude update

echo "Installing Required Packages"
aptitude install gcc make libreadline6-dev libghc-zlib-dev chkconfig

##GCC - As postgreSQL is written in C, we need C compiler to run postgreSQL.
##Libreadline - It enables history, vi editor, last line executed. It is library.
##Zlib - Enables compressing and decompressing of data stream.

echo "Adding User for Postgres server"

read -s -p "Enter password for postgres: " password
echo $password |passwd --stdin postgres
chage postgres -M -1

echo "Getting and extracting source code"
cd /usr/local/src
wget http://ftp.postgresql.org/pub/source/v9.6.6/postgresql-9.6.6.tar.gz
wget http://pgfoundry.org/frs/download.php/2449/pgtune-0.9.3.tar.gz
tar -xzvf /usr/local/src/postgresql-9.6.6.tar.gz
tar -xzvf /usr/local/src/pgtune-0.9.3.tar.gz

echo "Modifying Source Code file"

/usr/bin/perl -i -p -e 's/\#define FUNC_MAX_ARGS\s*100/\#define FUNC_MAX_ARGS           400/' /usr/local/src/postgresql-9.6.6/src/include/pg_config_manual.h
/usr/bin/perl -i -p -e 's/\#define INDEX_MAX_KEYS\s*32/\#define INDEX_MAX_KEYS          130/' /usr/local/src/postgresql-9.6.6/src/include/pg_config_manual.h

echo "Installing Postgres"
cd /usr/local/src/postgresql-9.6.6/
/usr/local/src/postgresql-9.6.6/configure --prefix=/tmp/
make
make install
##CONFIGURE THE SOFTWARE - It makes sure, build completes successfully by making sure all the dependencies required to build are present in the system. It needs C compiler to run as OS is mostly written in C
##BUILD THE SOFTWARE - It runs series of task written in makefile to build the finishes program from its source code. The tarball don’t include the finished makefile. Only after configure it is made ready as per our system.
##INSTALL THE SOFTWARE - make install make sure command will copy the built program, and its libraries and documentation, to the correct locations. Since the install step is also defined in the Makefile, where the software is installed can change based on options passed to the configure script, or things the configure script discovered about your system.

cd /usr/local/src/
cp /usr/local/src/postgresql-9.0.4/contrib/start-scripts/linux /etc/init.d/postgresql
chmod a+x /etc/init.d/postgresql

update-rc.d postgresql defaults

echo "PATH=$PATH:/usr/local/pgsql/bin"  >> /etc/profile
echo "LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib" >> /etc/profile

source /etc/profile

ln -s /usr/local/pgsql/bin/psql /usr/bin/psql
ln -s /usr/local/pgsql/bin/pg_dump /usr/bin/pg_dump
ln -s /usr/local/pgsql/bin/pg_dumpall /usr/bin/pg_dumpall
ln -s /usr/local/pgsql/bin/vacuumdb /usr/bin/vacuumdb

echo "Initialize Database"
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres -c "/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data"

cat >> /usr/local/pgsql/data/pg_hba.conf <<"DELIM"
host    all         all         192.168.160.0/23       md5
host    all         all         192.168.150.0/24       md5
host    all         all         192.168.99.0/23       md5
host    all         all         192.168.172.0/22       md5
host    all         all         192.168.122.0/24       md5
DELIM

echo "Tuning Postgres Server"
mkdir /var/log/pg_log
chown postgres /var/log/pg_log
/usr/bin/perl -i -p -e "s/\#listen_addresses = 'localhost'/listen_addresses = '*'/;" /usr/local/pgsql/data/postgresql.conf

/usr/local/src/pgtune-0.9.3/pgtune -i /usr/local/pgsql/data/postgresql.conf -T Web -c 200 -o /usr/local/pgsql/data/postgresql.conf

echo "kernel.shmmax = 6442450944" >> /etc/sysctl.conf  -- 6GB will be used by postgres/8GB
echo "kernel.shmall = 1572864" >> /etc/sysctl.conf

/sbin/sysctl -p -- Immediately make the effect in shmmax.


Data Directory layout:


  1. Global - Have all cluster wide tables like pg_database, pg_locks, pg_tables
  2. Base - Have all the Database with number, which are OID.
  3. Select datname,OID from pg_database; - It will show which database name and its OID which is stored inside base.
  4. Toast Table - Table that have columns potentially large entries will have an associated toast table, which is used for out-of-line storage of field values that are too large to keep in the table rows proper.
  5. Pg_tabspc - Tablespace is soft link to different mount point. \db - all tablespace. Default is under pg_default(base) and pg_global which is inside global directory.

    Create tablespace tablespace_name location ‘/tmp’;
    create database db6 tablespace tab1;
    Create table tab11(id number) tablespace tab1;

    Same directory can be used for two different tablespace with different database. Already DB is created and tablespace is default, we created new table in new tablespace, it will create table in different location as specified of tablespace. It creates a soft link from base to new location. All soft links are in pg_tabspc
  6. Select pg_relation_filepath(‘table_name’); - to find out location of table
  7. 8192 bytes(8KB) is the size of table initially because block size is 8 KB, then 16 KB, 24 KB and move on. 
  8. Select relfilenode from pg_class where relname=’table_name’; - OID of the table_name.
  9. Pg_xlog - All transaction logs/segments are here. It is also write ahead logs. Size is 16MB. While installing we can define location of pg_xlog.
  10. NOTE - netstat -an - It will dispaly port for various program like postgres where port is listening to.
  11. Pg_log - All logs gets logged and also startup.log.
  12. Pg_clog, pg_multiexact, pg_snapshot, pg_stat, pg_notify, pg_repslot, pg_logical, pg_dynshmem - Multiple directories containing different status data - temporary information. Checkpoint information.
  13. Pg_snapshots - Share data between 2 sessions.
  14. Page:Every table and index is stored as an array of pages of a fixed size (usually 8Kb, although a different page size can be selected when compiling the server). In a table, all the pages are logically equivalent, so a particular item (row) can be stored in any page. In indexes, the first page is generally reserved as a metapage holding control information, and there may be different types of pages within the index, depending on the index access method.
PG_VERSION
A file containing the major version number of PostgreSQL
base
Subdirectory containing per-database subdirectories
global
Subdirectory containing cluster-wide tables, such as pg_database
pg_clog
Subdirectory containing transaction commit status data
pg_multixact
Subdirectory containing multi transaction status data (used for shared row locks)
pg_notify
Subdirectory containing LISTEN/NOTIFY status data
pg_serial
Subdirectory containing information about committed serializable transactions
pg_snapshots
Subdirectory containing exported snapshots
pg_stat_tmp
Subdirectory containing temporary files for the statistics subsystem
pg_subtrans
Subdirectory containing subtransaction status data
pg_tblspc
Subdirectory containing symbolic links to tablespaces
pg_twophase
Subdirectory containing state files for prepared transactions
pg_xlog
Subdirectory containing WAL (Write Ahead Log) files
postmaster.opts
A file recording the command-line options the server was last started with
[postgres@ip-172-31-28-103 data]$ cat postmaster.opts
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"
postmaster.pid
A lock file recording the current postmaster process ID (PID), cluster data directory path, postmaster start timestamp, port number, Unix-domain socket directory path (empty on Windows), first valid listen_address (IP address or *, or empty if not listening on TCP), and shared memory segment ID (this file is not present after server shutdown)
[postgres@ip-172-31-28-103 data]$ cat postmaster.pid
14439
/usr/local/pgsql/data
1480396151
5432
/tmp
*
 5432001    491520


No comments:

Post a Comment

Subscribe to: Posts (Atom)