Category Archives: PostgreSQL

Show postgres lock

https://wiki.postgresql.org/wiki/Lock_Monitoring SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS … Continue reading

Posted in Database, PostgreSQL | Leave a comment

Check all table size in Postgres

The “tables ” is in information_schema The “pg_class” is in System Catalogs 1234567SELECT ist.TABLE_NAME, reltuples AS "entries", pg_size_pretty(relpages::BIGINT*8*1024) AS SIZE FROM information_schema.TABLES ist INNER JOIN pg_class c ON (ist.TABLE_NAME = c.relname) WHERE ist.table_catalog=’ip2_sync_master’ AND ist.table_schema=’public’ ORDER BY relpages DESC, ist.TABLE_NAME … Continue reading

Posted in Database, PostgreSQL | Leave a comment

Synchronize a postgres table through bash and csv

Please note that the csv export process does not escape commas. 1234567891011121314151617181920212223242526272829303132333435#!/bin/bash DIR=’/root/sql_dump’ mkdir -p "$DIR" cd "$DIR" SIZE=100000 #START=611244350 START=0 END=$((START + SIZE)) STOP=189097000 TABLNAME="schema.tablename" while [[ $START -lt $STOP ]] && [[ $END -le $STOP ]]; do   … Continue reading

Posted in Database, PostgreSQL | Leave a comment

Export and Import Postgres query to CSV

# =========================================================== # Export to CSV # =========================================================== # export PGPASSWORD=”YOURPASSWORD” # psql -U YOURUSERNAME -h YOURHOSTNAME -d YOURDBNAME-t -A -F”,” -c “select * from YOURTABLENAME limit 2″ > output.csv # =========================================================== # Import # =========================================================== # export PGPASSWORD=”YOURPASSWORD” # … Continue reading

Posted in Database, PostgreSQL | Leave a comment

Postgres SSD optimization

If you’re running your database on an SSD instead of a spinning disk, you might want to optimize postgres table space cost: 12345678910– Change the tablespace cost ALTER TABLESPACE pg_default SET ( seq_page_cost = 20,  random_page_cost = 1 ); — … Continue reading

Posted in Database, PostgreSQL | Leave a comment

Delete Postgres Cache

1234#!/bin/bash sync echo 1 > /proc/sys/vm/drop_caches service postgresql-9.3 restart

Posted in Database, PostgreSQL | Leave a comment

Check which database object depends on (has reference to) your table

12345678910111213141516SELECT R.TABLE_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK     ON U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG     AND U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA     AND U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE R     ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG     AND … Continue reading

Posted in Database, PostgreSQL | Leave a comment

Postgres function to devide-and-conquer (iterate) your big query

Create a postgres function to devide-and-conquer (iterate) your big query 1234567891011121314151617181920212223242526272829303132333435363738394041TRUNCATE TABLE YOUR_NEW_BIG_TABLE; DROP FUNCTION IF EXISTS pg_iterator(); CREATE OR REPLACE FUNCTION pg_iterator()   RETURNS void AS $BODY$ DECLARE     vOffsetRecord INT;     vTotal INT;     vLimit … Continue reading

Posted in Database, PostgreSQL | Leave a comment

Vacuum the Whole Database in Postgres

Postgres comes with a functionality called vacuum. Vacuum is intended to cleanup dead tuples or rows. This is how you cleanup the entire DB in PostgreSQL 1/usr/pg9/bin/vacuumdb –full –port=5433 –username=YOUR_USERNAME –password DB_NAME

Posted in Database, PostgreSQL | Leave a comment

Backup and Restore Postgres Using pg_dump and psql

This is how you backup your Postgres Database To Backup 1pg_dump –host=localhost –port=5432 –username=postgres –file=YOUR_FILENAME.sql YOUR_DB_NAME There are other options such as if you’re interested only on the structure then you can add: 1pg_dump –host=localhost –port=5432 –username=postgres –schema-only –format=p –create … Continue reading

Posted in Database, PostgreSQL | Leave a comment