Postgresql
Jump to navigation
Jump to search
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
- 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
- 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;