TutorialDBA - Support | Training | Consultant

I. TAKING TABLE STRUCTRE BACKUP WITH THEIR’S OBJECT FILE NAME : Below scripts helpful to take backup (DDL’s) of procedure, function & table definition with individual file system (object name) based on postgres schema . 1.  Windows Script for taking table definition with their object dump file name : Below windows script will be helpful to take the backup of all the table with individual file . D: cd D:\PostgresPlus\9.4AS\bin @echo off SET TableListeFile=D:\InCampusDB\HealthCraft_LC\schemas\wards\Tables\database_list.txt REM Saveing all tables name of database test_db on a temp file: database_list.txt psql -U enterprisedb -d HealthCraft_LC -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema='wards'" -o "%TableListeFile%" REM Loop on liste tables name: FOR /F "tokens=*" %I IN (%TableListeFile%) DO ( REM Dump each table on file pg_dump -U enterprisedb -st wards.%I HealthCraft_LC > "D:\InCampusDB\H...

I.TAKING TABLE STRUCTRE BACKUP WITH THEIR’S OBJECT FILE NAME : Below scripts helpful to take backup (DDL’s) of procedure, function & table definition with individual file system (object name) based on postgres schema . 1.Windows Script for taking table definition with their object dump file name : Below windows script will be helpful to take the backup of all the table with individual file . D: cd D:\PostgresPlus\9.4AS\bin @echo off SET TableListeFile=D:\InCampusDB\HealthCraft_LC\schemas\wards\Tables\database_list.txt REM Saveing all tables name of database test_db on a temp file: database_list.txt psql -U enterprisedb -d HealthCraft_LC -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema='wards'" -o "%TableListeFile%" REM Loop on liste tables name: FOR /F "tokens=*" %I IN (%TableListeFile%) DO ( REM Dump each table on file pg_dump -U enterprisedb -st wards.%I HealthCraft_LC > "D:\InCampusDB\HealthCraf...

In this shell script will help you to take the backup of postgresql table definition with their table file name automatically.  The table DDL backup will be taken automatically with seperate file name means table file, # Config: DB="DBA" U=enterprisedb export PGPASSWORD="tcs45" export PGPORT="5444" export PGHOST="20.0.4.101" TABLES="$(/opt/edb/as9.6/bin/psql -d $DB -U $U -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema='billing'")" for table in $TABLES; do echo backup $table ... /opt/edb/as9.6/bin/pg_dump -d $DB -U $U -w -st billing.$table > /home/Admin/2ndquadrant.in/HealthCraft_DC/schemas/billing/tables/billing.$table; done; cd /home/Admin/2ndquadrant.in/HealthCraft_DC/schemas/billing/tables ls -lh echo done Copy Note :  Here  billing  is the schema name  DBA  is the database name so you can change the database and schema name as per your convenient.

In this Shell script will displays schema name and table name in which table having more than dead tubles in a postgresql server How to create & run shell script file ? create  a shell script file here created tables_dead_tubles.sh using vim editor and change the environment variable as well as db name as per your environment, i - for inserting mode then paste the following script then press Esc button then enter :wq for saving script then run the script using sh tables_dead_tubles.sh cat tables_dead_tubles.sh echo " " echo " " HOSTNAME=`hostname` PSQL="/opt/PostgreSQL/9.3/bin/psql" PORT=5432 HOST="localhost" DB="template1" USER="postgres" echo "Enter the dead tuble count for example if you give 5000 means the script will dispays the schema name as well as table name How many table having more than 5000 dead tubles in database "; read count echo "------***WHAT ARE TABLES HAVING MORE THAN $count DEAD TUB...

This scripts are using to tar(zip compress) and remove more than 1 month old postgresql log and linux log file 1.This script used for moving and compressing  older than 30 days log files: vi /home/postgres/eds/tarlog.sh find /data/emut16_slavedata/pg_log/ -mtime +30 | xargs tar -czvPf /pg_xlog/older_log.tar.gz vi /home/postgres/eds/removelogs.sh find /data/emut16_slavedata/pg_log/ -type f -name "*.log" -mtime +30 -exec rm {} \; but before that get a listing to see what you are about to delete and tar find /data/emut16_slavedata/pg_log/ -type f -name "*.log" -mtime +30 -exec ls -tr {} \; 2.This script used for moving and compressing  older than 30 minutes and hours  log files: vi /home/postgres/eds/tarlog.sh find /data/emut16_slavedata/pg_log/ -cmin +30 | xargs tar -czvPf /pg_xlog/older_log.tar.gz vi /home/postgres/eds/removelogs.sh find /data/emut16_slavedata/pg_log/ -type f -name "*.log" -cmin +30 -exec rm {} \; but before that get a listing ...

Currently we want to take the backup what are the schema name having kafi and nijam. PreRequest: checking table rows count of particular table. Check the schema's size. check the available  backup location size. 1.List the schema whoose name start with kafi AND nijam: mhrorsin=# \dn+ kafi* List of schemas Name | Owner | Access privileges | Description -----------------------+-----------------+------------------------------------+------------- kafi | benz | benz=UC/benz+ | | | emutation=UC/benz+ | | | umhrorsin=UC/benz | kafi_audit | benz | benz=UC/benz+ | | | emutation=UC/benz+ | | | umhrorsin=UC...