Helpful Postgres Statements

 Find to 20 largest tables in the database

 SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

Drop existing connections for a particular database

This will drop existing connections:
Query pg_stat_activity and get the pid values you want to kill and issue select pg_terminate_backend(pid int) to them.

PostgreSQL 9.1 and below:

SELECT pg_terminate_backend(pg_stat_activity.procpid)

FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB';

PostgreSQL 9.2 and above:

SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB';

Creating a user to access a server

The idea is to create a user and assign the user with permissions to access a single database from a particular IP source. They will be able to access on the port set out.

• set up user in postgres (using super user account)

CREATE USER user234 WITH PASSWORD '3f5ffg#' VALID UNTIL '2014-04-01';
GRANT CONNECT ON DATABASE mydb TO user234;
GRANT SELECT ON table23 TO user234;
GRANT SELECT ON table24 TO user234;
GRANT SELECT ON table25 TO user234;
GRANT SELECT ON ALL TABLES TO user234; -- bah.. just give permission to all 

• edit # vi /var/lib/pgsql/9.1/data/pg_hba.conf

host    mydb   user234            172.23.20.32/24         md5

• Reload conf as postgres user

/usr/pgsql-9.1/bin/pg_ctl reload -D /var/lib/pgsql/9.1/data/

Datetime Setting

The problem lies because of the date/time field settings..

to fix this we can first show the datestyle in the database..

=> SHOW datestyle;
 DateStyle
-----------
 ISO, YMD
(1 row)

Then we can set the datestyle to the correct style..

=> SET datestyle = "ISO, DMY";
SET

=> SHOW datestyle;
 DateStyle
-----------
 ISO, DMY
(1 row)

Restart Postgres

/etc/init.d/postgresql restart

OR

service postgresql restart

Set the timezone in a PSQL DB

=> SET TIME ZONE 'Australia/Victoria';
SET

=> SHOW TIME ZONE;
      TimeZone
--------------------
 Australia/Victoria
(1 row)

PSQL commands to file

psql -U user234 -d database -H -c "select * from ...;" -o /home/me/file.html

Database Backup

Simple backup which is compressed.

pg_dump -U user234 mydb | gzip > /home/me/backups/mydb_YYYMMDD.sql.gz

exclude a table

pg_dump -U user234 --exclude-table=tabletoexclude mydb | gzip > /home/me/backups/mydb_YYYMMDD.sql.gz

Schema Only

pg_dump -U user234 --schema-only -f mydb_backup_schema_YYYMMDD.sql mydb

Database Restore

Restore from the backup made

psql -U postgres
DROP DATABASE mydb; CREATE DATABASE mydb OWNER user234;\q
gunzip -c /home/me/backups/mydb_YYYMMDD.sql.gz | psql -U user234 mydb;

Database Clone

Clone the database, an easy way to set up a clone to test with.

CREATE DATABASE newdb2 WITH TEMPLATE mydb;

The following will drop the database and copy back the original one. This doesn't really follow with the previous statements, so read carefully.

DROP DATABASE mydb;
CREATE DATABASE mydb WITH TEMPLATE mydb_original;
ALTER DATABASE mydb OWNER TO user234;