#Installing
1. apt install postgresql postgresql-contrib

#Checking if PostreSQL service running
2. systemctl status postgresql

#Setting password for linux user 'postgres'
3. passwd postgres

#Setting password for DB user 'postgres'
4. sudo -i -u postgres
5. psql
6. ALTER USER postgres WITH PASSWORD 'your_password';

#Listening on all network interfaces for remote management
7. /etc/postgresql/12/main/pg_hba.conf:
host all all 0.0.0.0/0 md5

/etc/postgresql/12/main/postgresql.conf:
listen_addresses = "*"

systemctl restart postgresql

#Create database and user
#DB
1. CREATE DATABASE dbname;

#User
2. CREATE USER dbuser WITH PASSWORD 'password';

#Privileges
3. GRANT ALL PRIVILEGES ON DATABASE dbname to dbuser;

#For bulk load (COPY command)
4. GRANT pg_read_server_files to dbuser;

#Management:

#Databases list
\list (or \l)

#Tables list
\dt

#Columns list
\d+ tablename

#Change DB
\c dbname

#bulk insert

ALTER TABLE target table SET UNLOGGED;
#bulk data insert operations…
ALTER TABLE LOGGED;

#optimize
VACUUM (FULL, VERBOSE, ANALYZE);

#mass insert

INSERT INTO target_table (column1, column2, …, column_n)
VALUES
(value a, value b, …, value x),
(value 1, value 2, …, value n),
(value A, value B, …, value Z),
(value i, value ii, …, value L),
...;

#php + pgsql + xml reader

apt install php

apt install php-pgsql

apt install php-xml

# dump all DB

pg_dump db > db.sql

# dump only schema

pg_dump db --schema-only > db.sql

# dump only one table

pg_dump db --table mytable > db.sql

# restore

cat db.sql |psql -d db