Startup

Macos

brew services start postgresql@14
brew services restart postgresql@14

Docker

Download Postgres Image

docker pull postgres

Create Container from Image

docker run --name postgresql -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=admin -p 5432:5432 -v Users/ashirali-mg/Documents/postgres-data -d postgres

• PostgreSQL is the name of the Docker Container.

• e POSTGRES_USER is the parameter that sets a unique username to the Postgres database.

• e POSTGRES_PASSWORD is the parameter that allows you to set the password of the Postgres database.

• p 5432:5432 is the parameter that establishes a connection between the Host Port and Docker Container Port. In this case, both ports are given as 5432, which indicates requests sent to the Host Ports will automatically redirect to the Docker Container Port. In addition, 5432 is also the same port where PostgreSQL will be accepting requests from the client.

• v is the parameter that synchronizes the Postgres data with the local folder. This ensures that Postgres data will be safely present within the Home Directory even if the Docker Container is terminated.

• d is the parameter that runs the Docker Container in the detached mode, i.e., in the background. If you accidentally close or terminate the Command Prompt, the Docker Container will still run in the background.

• Postgres is the name of the Docker image that was previously downloaded to run the Docker Container.

Verify

docker ps

Get Connection Details

docker inspect postgresql

VPS Setup

Install

Installation Using Package Manger

sudo apt install postgresql postgresql-contrib

Installation From APT Repository

Run each command seperately

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

sudo apt-get -y install postgresql

Test

sudo -u postgres psql
SELECT version();

Connect

sudo su postgres
psql

Connect Remote DB

psql -h hostname_or_ip -p port -d database_name -U username

Install pgAdmin on VPS

Run each command seperately

curl -fsSL https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgadmin.gpg

sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'

sudo apt update

sudo apt install pgadmin4

sudo /usr/pgadmin4/bin/setup-web.sh

Connect (in browser)

ip_address/pgadmin4

Create User

1. Switch to the PostgreSQL user

sudo -i -u postgres

2. Access the PostgreSQL interactive terminal

psql

Above two commands in one:

3. Create a new PostgreSQL user

CREATE USER new_user WITH PASSWORD 'password';

4. Create a new PostgreSQL database

CREATE DATABASE new_database;

5. You will set the default character encoding to UTF-8, which Django expects. You are also setting the default transaction isolation scheme to “read committed”, which blocks reads from uncommitted transactions. Lastly, you are setting the timezone. By default, Django projects will be set to use UTC. These are all recommendations from the Django project itself:

ALTER ROLE db_admin SET client_encoding TO 'utf8';
ALTER ROLE db_admin SET default_transaction_isolation TO 'read committed';
ALTER ROLE db_admin SET timezone TO 'UTC';

6. Grant privileges to the new user

GRANT ALL PRIVILEGES ON DATABASE new_database TO new_user;

6.a. Manage Privileges For A Role

REVOKE ALL PRIVILEGES ON *.* FROM 'yourusername'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES ON `yourdatabase`.* TO 'yourusername'@'%';

7. Exit From Psql

   \q

Config Entry

pg_hba.conf

Usually in

/etc/postgresql/14/main/pg_hba.conf

When edited

sudo systemctl restart postgresql

Interface

psql -U postgres
Password: admin

docker exec -it <container_id> bash
psql -U postgres

General Queries

\l : List all databases available

\c: Select a database

\dt: Show tables in database

Create

Create Database

CREATE DATABASE pern_todo;

Create Table

CREATE TABLE todo (
    todo_id SERIAL PRIMARY KEY,
    description VARCHAR ( 255 )
);

UUID as ID

CREATE TEMP TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

Drop

All Tables

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

//OPTIONAL:
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

Database

DROP DATABASE ucem_db;

Delete Trigger

Syntax:

DROP TRIGGER trigger_name ON table_name

Master & Slave

Achieving PostgreSQL Master Slave Replication: 7 Easy Steps

PostgreSQL is an extremely robust open-source database used by noted players like Skype, Reddit, Instagram, and OpenStreetMap. As the traffic on websites and applications increases the frequency of reading and writing data from the Database increases and so does the load. PostgreSQL allows replication to nodes that can run read-only queries.

https://hevodata.com/learn/postgresql-master-slave-replication/

Backup

Backup From Docker

pg_dump -U postgres -W -Fp msigma_db -f /tmp/msigma.sql

docker cp postgresql:/tmp/msigma.sql /Users/ashirali-mg/Work/Msigma

Best Practices

Nine ways to shoot yourself in the foot with PostgreSQL

Previously on Extreme Learning, I discussed all the ways I've broken production using healthchecks. In this post I'll do the same for PostgreSQL.

https://philbooth.me/blog/nine-ways-to-shoot-yourself-in-the-foot-with-postgresql