PostgreSql

1

sudo apt install postgresql postgresql-contrib

1
2
3

$ psql -h 127.0.0.1 -p 5432 -U www
> \l # list all databases
> \du # list all users

1
2

CREATE USER user-name WITH PASSWORD 'change-me';
CREATE DATABASE db-name WITH ENCODING = 'UTF8' OWNER user-name;

1
2

pg_dump -Fc --no-acl --no-owner -h localhost -U myuser -d mydb -f mydb.dump
psql -h 127.0.0.1 -p 5432 -U newuser newdb < mydb.dump

1

SELECT client_addr, state FROM pg_stat_replication;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- by database
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;
-- by table
select relname, pg_size_pretty(pg_relation_size(relid)) as size from pg_stat_user_tables;
-- by table and index
select relname, pg_size_pretty(pg_total_relation_size(relid)) as size from pg_stat_user_tables;
-- by table
select pg_size_pretty(pg_total_relation_size('tab')) as size;
-- by index
select pg_size_pretty(pg_relation_size('myindex')) as size;
-- by tablespace
select spcname, pg_size_pretty(pg_tablespace_size(spcname)) as size from pg_tablespace;

1
2

max_connections = 1000
shared_buffers = 240MB

1

ALTER DATABASE template1 REFRESH COLLATION VERSION

1

local   all             XXX                                md5