[ Home | About | Licence | About author | Contact ]
Written by Oleksandr Gavenko (AKA gavenkoa), compiled at 2015-10-05
from rev 764bb173578d
.
Postgre.¶
Contents
Installing on Debian.¶
Install and create new user and database:
$ sudo apt-get install postgresql postgresql-client
$ sudo su - postgres
% psql
postgres=# CREATE USER "mypguser" WITH PASSWORD 'mypguserpass';
postgres=# CREATE DATABASE "mypgdatabase" OWNER "mypguser";
postgres=# \q
Connect as user mypguser
to new database:
$ su - mypguser
$ psql mypgdatabase
In order to create local host superuser:
$ sudo su - postgres
$ createuser --superuser USER
$ exit
$ sudo -u USER psql
- https://wiki.debian.org/PostgreSql
- Debian wiki instructions.
List databases, schemas and tables.¶
Default database is postgres
.
To list databases and database locales:
$ psql -U pgadmin -l
or:
=> SELECT datname FROM pg_database WHERE datistemplate = false;
=> \l
To switch databases:
=> \connect NAME
Schemas:
=> select schema_name from information_schema.schemata;
=> select nspname from pg_catalog.pg_namespace;
=> \dn *
To list all tables in the current database:
=> SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;
=> \dt
Set default schema.¶
set search_path to NAME;
Database, table, index size.¶
Database size:
SELECT pg_database_size('geekdb'); -- in bytes
SELECT pg_size_pretty(pg_database_size('dbname'));
List of databases sizes:
\l+
List tables sizes:
\d+
Table total size (with indexes):
SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename'));
Sole table size (without indexes and other):
SELECT pg_size_pretty(pg_relation_size('schemaname.tablename'));
Largest table in the PostgreSQL database:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
Using psql client.¶
Using password file ~/.pgpass
:
# comment
hostname:port:database:username:password
hostname:port:*:username:password
hostname:*:*:username:password
Connect by:
$ psql -U $USER -h $HOST $SCHEMA
How to view execution plan:
EXPLAIN query;
EXPLAIN ANALYZE query;
How to redirect the output of query to a file:
\o output_file
SELECT * FROM pg_class;