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 madepsql -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;
