postgres common operations

postgres.sh

# Access console
sudo -u postgres psql


# Backup and restore database

su - postgres
pg_dump dbname > dbname.bak
# without owner
pg_dump database_name -O -x > output_file 
dropdb dbname
createdb dbname
psql test < dbname.bak

# backup command from DBeaver
C:\Program Files\PostgreSQL\13\bin\pg_dump.exe --verbose --host=127.0.0.1 --port=22411 --username=orthoray --format=c --no-privileges --no-owner --file D:\Mirror\Downloads\orthoray20220319 -n "public" orthoray
C:\Program Files\PostgreSQL\13\bin\pg_restore.exe --verbose --host=127.0.0.1 --port=54405 --username=zapatos --clean --no-owner --format=c --dbname=zapatos D:\Mirror\Downloads\orthoray20220319

# Install postgres

sudo apt install python-pip python-dev libpq-dev postgresql postgresql-contrib nginx curl libmysqlclient-dev python3-mysqldb
sudo -u postgres psql
CREATE DATABASE myproject;
CREATE USER myproject WITH PASSWORD 'password';
ALTER ROLE myproject SET client_encoding TO 'utf8';
ALTER ROLE myproject SET default_transaction_isolation TO 'read committed';
ALTER ROLE myproject SET timezone TO 'America/Lima';
GRANT ALL PRIVILEGES ON DATABASE myproject TO myproject;
ALTER USER myprojectuser WITH PASSWORD 'myprojectuser';
\q