Postgresql

psql

run command from cli

psql -c "SELECT datname FROM pg_database;"

Connect to remote database:

export PGPASSWORD=password && psql --host=10.111.222.1 --port=5492 --username=user --no-password postgres

install psql

apt-get install freetds-bin

List databases

Alternative 1

SELECT datname FROM pg_database;

Alternative 2

gives more information too.

\l
    Name     |    Owner    | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------+-------------+----------+-------------+-------------+-----------------------
 indata_mdb  | uc_etl_prod | UTF8     | sv_SE.UTF-8 | sv_SE.UTF-8 |
...

select database

\c testdb;

show tables

List all tables in all schemas

\dt *.*

list tables in current schema

\dt

Or from command line.

su - postgres -c "psql -c \"\\dt \" database"

schema/search_path

List current schema

SHOW search_path;

Change search path.

SET search_path TO myschema, public;

describe table

\d table;

Change view. One line per attribute

\x on

exit/quit

\q

update

UPDATE users SET admin = 1 WHERE user_id = 14002;

Create database

CREATE DATABASE dbname OWNER username;

Delete database

DROP DATABASE dbname;

delete database from cli

dropdb $dbname

Import a database

psql username  -h hostname -d dbname < dump.sql

Create user

CREATE USER username WITH PASSWORD 'MYPASS';

Set super privileges

ALTER USER username WITH SUPERUSER;

List users

\du

Deleting user

DROP USER nomusr

Getting help

\? or \h

size of database

SELECT pg_size_pretty( pg_database_size('database') );

size of table

SELECT pg_size_pretty( pg_total_relation_size('tablename') );

clean archives older than 2 days

su postgres -c "/usr/pgsql-9.5/bin/pg_archivecleanup /var/lib/pgsql/9.5/archivedir/ $(basename $(find /var/lib/pgsql/9.5/archivedir/ -ctime +2 | tail -n 1))"

backup

1. Backup database.

DATABASE=very_nice_database ; pg_dump -Fc -Z 2 --file=/tmp/${DATABASE}.$(date '+%Y%m%d').dump ${DATABASE}

restore

Look at permissions on database

psql -c "\l"
export DATABASE=very_nice_database

Drop database connectipons.

psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '${DATABASE}' AND pid <> pg_backend_pid();"

Drop database.

dropdb ${DATABASE}

Create database

createdb -T template0 ${DATABASE}

Restore database

pg_restore -j8 -d ${DATABASE} --no-privileges --no-owner --clean --if-exists --exit-on-error /tmp/${DATABASE}.$(date '+%Y%m%d' --date "-4 days").dump

1. Set correct permissions.

psql -c "ALTER DATABASE ${DATABASE} OWNER TO ${OWNER};"

restore database from backup

createdb -T template0 '${dbname}'
gunzip -c ${filename.gz} | psql ${dbname}

bookmarks

https://wiki.postgresql.org/wiki/Disk_Usage

clear space

\c database
vacuum;

if that doesn't work do:

vacuum: freeze; or vacuum full;

autovacuum

Is autovacuum enabled.

SHOW autovacuum;

count table

SELECT COUNT(*) FROM TABLE_NAME;

If you don't need an exact count, the current statistic from the catalog table pg_class might be good enough and is much faster to retrieve for big tables.

SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'table_name';

nuber of connections

su - postgres -c "psql -t -c 'SELECT * FROM pg_stat_activity;'" | wc -l

drop connections

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'dbname' AND pid <> pg_backend_pid();

pg_hba.conf

Connections are configured in this file.

list prepared transactions

postgres@cti_batch=# select * from pg_prepared_xacts where database='cti_batch';

remove prepared connecion

ROLLBACK PREPARED '131077_AAAAAAAAAAAAAP//rB6PFn41NfBevSfGABG7nTE=_AAAAAAAAAAAAAP//rB6PFn41NfBevSfGABG7pAAAAAIAAAAA';

pager off

\pset pager off

pager

export PAGER=less

config file

.my.cnf

.pgpass
hostname:port:database:username:password