Postgre SQL - Howto's | vitrubio.net

Postgre SQL

Jorge
3 mins to read
postgresql postgres database db psql
Home / Postgre SQL

PostgreSQL

first install

apt install postgresql

become postgres user

su - postgres

enter postgres console

psql

create db

createdb DBNAME -l en_US.UTF-8 -0 postgres -E UTF8

create user for db

createuser --no-inherit --createdb DBNAME
createuser --superuser --no-inherit --no-createrole --no-createdb --pwprompt USERNAME

start using the db

postgres@newsletters:/var/www/listmonkonthefly$ psql

list databases

\l

will output the dbs something like like

                                       List of databases
      Name      |     Owner      | Encoding |   Collate   |    Ctype    |   Access privileges   
----------------+----------------+----------+-------------+-------------+-----------------------
 dbname01       | dbuser01       | UTF8     | C           | C           | 
 dabname02      | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres       | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0      | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                |                |          |             |             | postgres=CTc/postgres
 template1      | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
                |                |          |             |             | =c/postgres
(5 rows)

connect to database

\c <databasename>

connect as owner

\c <databasename> <databaseowner>

list definition terms of field

\dt

select table_name to be modified

SELECT * from table_name;

select ‘foo’ from bar;

SELECT * from table_name WHERE bar='foo';

modify values foo to faa

only one desired list using the WHERE lolo=<number> it will confirm all subscribers even unsubscribed ones

UPDATE subscriber_lists SET bar='faa' WHERE lolo=1;

quit

\q

Backup dump

https://www.postgresql.org/docs/current/backup-dump.html

dump

pg_dump dbname > dumpfile

restore dump

delete old db

drop dbname

create empty db before restore

createdb dbname

restore dump

psql dbname < dumpfile

postgres will continue on errors, if you want to stop when happens

psql --set ON_ERROR_STOP=on dbname < dumpfile

maintaining and analizing psql db

First list the databases \l and connect to the one you need \c <PSQLDBNAME> then check sized.

List all objects an their size

SELECT relname, pg_total_relation_size(oid)
FROM pg_class
ORDER BY 2 DESC;

All objects and their respective sizes

This should detail:

  • objectname – The name of the object
  • objecttype – r for the table, i for an index, t for toast data, …
  • entries – The estimated number of entries in the object (e.g. rows)
  • size – The size of the object
SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_class
WHERE relpages >= 8
ORDER BY relpages DESC;

sizes in /var/lib/postgresql/

check sizes in file system

du -hd 1 /var/lib/postgresql/13/main/base/

will output some data you want to check like

1.7G    /var/lib/postgresql/13/main/base/175429
480M    /var/lib/postgresql/13/main/base/199902

then from inside psql do

SELECT datname FROM pg_database WHERE oid = 175429;

will output the database name to wich belongs

 datname
---------
 <databasename> 
(1 row)

then connect to db \c <databasename and check it out

SELECT relname, relnamespace::regnamespace, relkind FROM pg_class WHERE relfilenode = 175429;

if it provides no info, then you might have to look at the files and cache tables check sizes of tables in bash, like this you’ll get a reverse listing by size of a directory

ls -lSrh /var/lib/postgresql/13/main/base/175429/

output might be:

-rw------- 1 postgres postgres  93M Mar 30 11:21 188886
-rw------- 1 postgres postgres 283M Mar 30 11:21 175678
-rw------- 1 postgres postgres 310M Mar 30 11:06 175915
-rw------- 1 postgres postgres 311M Mar 30 11:21 175990

then connect to the database \c <databasename> and see what about

SELECT relname, relnamespace::regnamespace, relkind FROM pg_class WHERE relfilenode = 175990;

output might be

 relname | relnamespace | relkind
---------+--------------+---------
 users   | public       | r
(1 row)

like this you’ll get to know where the big size is comming from.