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
psql -x -c "select * from blabla limit 1;"

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};"

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

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

Permissions in postgres

pg_hba.conf  local file         which is a local file that defines which sources for login credentials to use. (ldap, local users... to me it feels like nsswitch.conf)
owner        database           of database. The right to modify or destroy an object is always the privilege of the owner only.
role:s       separate database  are like user and groups combined. One role can inherit another. Different attributes gives various capabilities. login, superuser...
privileges   database           on (table, function...): SELECT , INSERT , UPDATE , DELETE
You can login with one role and then switch to another.

roles

Which roles exist.

\du

set new role

set role super-man;

current role

user name of current execution context

SELECT current_user;

session user

SELECT session_user;

display privileges

\dp
\z

tablespaces

List tablespaces

SELECT spcname FROM pg_tablespace;

delete rows matching value and timestamp

DELETE FROM receipt_transaction WHERE status_point = 'FAIL_POINT_CALC' and business_date  < ( now() - interval '+14 day' ) ;