Postgres DB Fundamentals

 

vacuum --> Defrag in Oracle

 

In postgres Tables gets Bloated due to DML's

A tuple is an internal representation of a row

 

Vacuum	Vacuum FULL
Free up dead rows for reuse, Selects and DMLs Allowed No Exclusive Locks, OS Space NOT released	Rewrite the table with no dead rows (Tuple), No DML’s Including select Allowed, Puts EXCLUSIVE Lock, OS Space will be released

Tune AutoVacuum:

set Vacuum_cost_page IO limit parameters
No. of Workers (I/O Intensive)
 

Autovacuum_vacuum_threshold :
min number of updates/deletes tuples needed to trigger a VACUUM in any table. The default is 50 tuples.

Autovacuum_vacuum_scale_factor:
Fraction of the table size (in terms of no. of rows) to decide whether vacuum should be triggered. Default is 0.2
 

SELECT * FROM pg_stat_activity WHERE query LIKE '%autovacuum%';

Parameter	Default	Meaning
autovacuum	on	Enables autovacuum
autovacuum_vacuum_scale_factor	0.2 (20%)	Vacuum when 20% of table is dead
autovacuum_analyze_scale_factor	0.1 (10%)	Analyze when 10% of table changes
autovacuum_vacuum_threshold	50	Minimum dead tuples before vacuum
autovacuum_analyze_threshold	50	Minimum changes before analyze
autovacuum_naptime	1 min	How often autovacuum checks tables
autovacuum_max_workers	3	Max parallel autovacuum workers
autovacuum_vacuum_cost_limit	-1 (use global)	Cost-based throttle
autovacuum_vacuum_cost_delay	20 ms	Delay between vacuum cycles

Transaction wraparound

·        TXID's can go up till ~4 billion (32 bit ID)

·        Postgres will stop accepting commands when there are fewer than one million transactions left before the maximum XID value is reached.

 

   Why Happens this ?

·        Auto-vacuum is set to turned off

·        Heavy DML Operation

·        Many session or connection’s holding lock’s for very long time

 

   What happens in this Situation

·        PostgreSQL will stop accepting DML statements and switches to READONLY mode.

 

   FIX:

·        Stop and Bring up DB into single user mode

·        Run Vacumm FULL on entire DB (vacuumdb --all)

·        Once done stop ad restart postgres normally

Postgres Backup/Restore

 

pg_dump    à Creates a backup of ONE database at a time

pg_dumpall  à Can back up ALL of your databases simultaneously,

 

pg_dump -U username -W -F t database_name > c:\backup_file.tar

 
-F : specifies the output file format that can be one of the following:
    ·        c: custom-format archive file format
    ·        d: directory-format archive
    ·        t: tar
    ·        p: plain-text SQL script file (Default)
-h Specify database server host
 
-p   Specify database server port
-U  Specify the user which is used to connect to the PostgreSQL database server
-W Used to prompt for a password before connecting to the PostgreSQL server
-d   Specify the database to dump
 
 
 

SCHEMA backup
 

pg_dump --username=user --password --schema-only [schema_name] > database_schema.sql

pg_dump --schema-only DATABASE > schema.sql

 
 
EX:
pg_dump --clean --create --file /tmp/DB-$(date +%Y%m%d).pgdump --format=custom --no-unlogged-table-data  <dbname>
 
 
psql --dbname=DBNAME --command="select pg_start_backup('CurBuild');"
 

Restore

To import a single database testdb from the tar dumpfile

pg_restore -c -U username -W -F t -d testdb dump.tar

 

To import ALL databases from tar dumpfile

pg_restore -c -U username -W -F t dump.tar

To import 1 database from .sql backup

postgres=# create database

psql   -d <New_DBNAME>    < dump.sql 

Validate :

-bash-4.2$ psql

psql (15.4)

Type "help" for help.

postgres=#  \l

postgres=# \c   <new_dbname>

postgres=#  SELECT pg_size_pretty( pg_database_size(‘NEW_DBNAME’));