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;