Install and Configure PostgreSQL on Ubuntu 18.04

pg_dump
pg_dumpall

Backup
mkdir backup
pg_dump okna > ~/backup/db_backup.sql
pg_dump db_name > db_backup.sql
pg_dumpall > cluster_backup.sql

Restore
psql okna < ~/backup/db_backup.sql
psql db_name < db_backup.sql
psql -f cluster_backup.sql postgres

Using Compression

Backup
pg_dump db_name | gzip > db_backup.gz
pg_dumpall | gzip > cluster_backup.gz

Restore
gunzip -c db_backup.gz | psql db_name
gunzip -c cluster_backup.gz | psql postgres

sudo -i -u postgres
Create Database postgres
createdb oboi
dropdb oboi
psql
psql oboi postgres
dropuser roduman

\q
\list
\l
\dt
\conninfo
\du


\ds - SEQUENCE
ALTER SEQUENCE "Products_Id_seq" RESTART WITH 1;

sudo nano /etc/postgresql/10/main/postgresql.conf
listen_addresses = '*'

sudo nano /etc/postgresql/10/main/pg_hba.conf
host    all             all             82.114.225.111/32       password
host    all             all             all       password
sudo systemctl restart postgresql

(END) - Go out

\d "Profils"
\d+ "Catalogs"

SELECT * FROM "Catalogs";
SELECT "Title" FROM "Murals";
SELECT "Id", "Title" FROM "Murals";

/var/lib/postgresql/backup
sudo cp CatalogProduct.csv /var/lib/postgresql/backup/CatalogProduct.csv

sudo apt update
sudo apt install postgresql postgresql-contrib

sudo -u postgres psql postgres
\password postgres
\q

COPY persons(first_name,last_name,dob,email) 
FROM 'C:\tmp\persons.csv' DELIMITER ',' CSV HEADER;

COPY "Catalogs" ("Sort", "Title", "Slug", "BrowserTitle", "Description", "Text") FROM '/var/lib/postgresql/backup/cats.csv' DELIMITER ',' CSV HEADER;
COPY "Murals" ("Sort", "Title", "BrowserTitle", "Description", "Img", "Alt", "VendorCode", "PresencePicture") FROM '/var/lib/postgresql/backup/murals.csv' DELIMITER ',' CSV HEADER;

DELETE FROM "Murals";
DELETE FROM "Catalogs";

COPY "Informations" ("Title", "BrowserTitle", "Description", "Text") FROM '/var/lib/postgresql/backup/pages.csv' DELIMITER ',' CSV HEADER;
COPY "Articles" ("Title", "BrowserTitle", "Description", "Text", "Img") FROM '/var/lib/postgresql/backup/articles.csv' DELIMITER ',' CSV HEADER;
COPY "Regions" ("Title", "Description", "Text") FROM '/var/lib/postgresql/backup/regions.csv' DELIMITER ',' CSV HEADER;

UPDATE "Reviews" SET "Active" = 'y';

ALTER TABLE ONLY "Murals" ALTER COLUMN "Sort" SET DEFAULT 500;
ALTER TABLE "Murals" ALTER COLUMN "Sort" TYPE INT USING "Sort"::integer;
ALTER TABLE "Areas" DROP COLUMN "Name";

crontab -e
17 10 * * * pg_dump -U postgres kitchen > ~/backup/$(date +\%m-\%d-\%Y-)kitchen.sql
date - get time and date

COPY "Locations" TO '/var/lib/postgresql/backup/locations.csv' DELIMITER ',' CSV HEADER;

Select all column rows from the database.

SELECT "ColumnName" FROM "TableName";

Update all rows.

UPDATE "TableName" SET "ColumnName" = REPLACE("ColumnName", 'OldValue', 'NewValue');

UPDATE "Murals" SET "CollectionId" = 36 WHERE "Id" IN (190, 191, 192, 193);

UPDATE "Interiors" SET "CollectionId" = 36 WHERE "Id" IN (25, 46);

UPDATE "Murals" SET "Sort" = '501' WHERE "Id" BETWEEN 3120 AND 3886;

Delete

DELETE FROM "Products" WHERE "Id" IN (11914, 12086);

DELETE FROM "Products" WHERE "Id" BETWEEN 11915 AND 12085;