Postgres SQL - keshavbaweja-git/guides GitHub Wiki

psql commands

# clear screen
\! clear

# List databases with access privileges
\l

# Choose database
\c <database-name>

# List schemas in current database with access privileges
\dn+

# List default access privileges for schemas in current database
\ddp

# List tables in current database
\dt

# List access privileges for a table
\dp <schema.table>

# List columns in table
\d+ <table_name>

SELECT * FROM information_schema.columns
WHERE table_schema = 'your_schema'
AND table_name = 'your_table';

# List roles
\du

# Change password
\password <user>
alter role <role-name> with password '<password>';

# Create user
create user <user-name> with password '<password>';

# Grant connect
grant connect on database <database-name> to <role-name>;

# Load dvdrental sample database
# 1. Download dvdrental.tar https://www.postgresqltutorial.com/postgresql-sample-database/
pg_restore \
--host=$HOST \
--port=$PORT \
--username=postgres \
--password \
--dbname=dvdrental \
~/Downloads/dvdrental.tar

# Load sample Russian flight db
# https://postgrespro.com/docs/postgrespro/10/demodb-bookings-installation.html
psql \
--host=$HOST \
--port=$PORT \
--username=postgres \
--password \
-a \
-f ../postgres-sample-db/demo-big-en-20170815.sql

# Create use with IAM authentication
create user jane;
grant all privileges on database dvdrental to jane;
grant all privileges on database demo to jane;
GRANT rds_iam TO jane;

Install on AL2

sudo amazon-linux-extras install postgresql13

Connect to Private RDS instance

https://aws.amazon.com/premiumsupport/knowledge-center/rds-connect-using-bastion-host-linux/

ssh -i "<path-to-private-key.pem>" -f -N -L \
5432:<rds-private-dns-name>:5432 \
ec2-user@<bastion-host-public-ip> -v
k --request-timeout='600s' \
-n fp1 run -ti --rm psql \
--image=jbergknoff/postgresql-client \
-- postgresql://<username>:<password>@<hostname>:5432/<db>

Export data as csv

https://dataschool.com/learn-sql/export-to-csv-from-psql/

Write Ahead Log

WAL Level

  • logical - allows logical change sets to be extracted from WAL records
  • replica - allows replication to a secondary server
  • minimal - allows recovery from a crash or immediate shutdown

fsync (bool)

  • Controls whether WAL records are written to disk, can result in unrecoverable data corruption if turned off. It should be turned off only if it is possible to recreate DB from external data.
    • Initial loading of a new database cluster from a backup file
    • Read only replica that is not failed over to

Continuous archiving

  • Also known as Log shipping or Warm standby
  • Offers a HA cluster with a standby server ready to take over operations if primary server fails
  • Primary server operates in continuous archiving mode while the standby server acts in continuous recovery mode.
  • Granularity - WAL records / file shipping (WAL segments)
  • A standby server can read WAL from a WAL archive or directly from master over a TCP connection (streaming replication)
  • Standby mode processing
    • apply WAL records from archive location
    • apply WAL records from pg_wal location
    • apply WAL records from streaming replication connection

High availability solutions

  • Shared disks
  • File system (block device) replication
  • WAL log shipping
  • Trigger based replication
  • SQL statement replication with a middleware
  • Asynchronous multi master replication
  • Synchronous multi master replication
⚠️ **GitHub.com Fallback** ⚠️