Skip to content

Latest commit

 

History

History
221 lines (175 loc) · 7.09 KB

postgresql.md

File metadata and controls

221 lines (175 loc) · 7.09 KB

PostgreSQL

Installation - Server

(1) Homebrew

  • Install - brew install postgresql@14
    • At successful installation this message - 'This formula has created a default database cluster with: initdb --locale=C -E UTF-8 /opt/homebrew/var/postgres'
  • brew services list - See which versions of postgresql have been installed and which version is running now
  • Start & Stop automatically now & on login
    • Start - brew services start postgresql@14
    • Stop - brew services stop postgresql@14
    • Restart - brew services restart postgresql@14
  • To switch versions:
    • brew services stop postgresql@14
    • brew services start postgresql@13
    • brew unlink postgresql@14
    • brew link postgresql@13
    • echo 'export PATH="/opt/homebrew/opt/postgresql@13/bin:$PATH"' >> ~/.zshrc
  • Initial setup
    • createuser -s postgres - create user postgres
      • By default a user is also created with the home user name i.e. shaunthomas
    • psql -U postgres - login postgres user
  • Binaries are in /opt/homebrew/opt/postgresql@14/bin
    • Default binaries postgres and psql are in /opt/homebrew/bin
  • Run not as daemon /opt/homebrew/opt/postgresql@14/bin/postgres -D /opt/homebrew/var/postgresql@14
  • References

Start & Stop manually

  • pg_ctl -D /usr/local/var/postgres start
  • pg_ctl -D /usr/local/var/postgres stop
  • pg_ctl -D /usr/local/var/postgres status
    • pg_ctl -D /opt/homebrew/var/postgresql@14 status
  • -D - data directory
  • If installed by @14 brew install command then data directory is in /opt/homebrew/var/postgresql@14
  • More options > https://www.postgresql.org/docs/current/app-pg-ctl.html

(2) Docker / docker-compose

  • Run commands
    • docker pull postgres:16-alpine
    • docker run --name postgres16 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16-alpine
    • docker exec postgres16 psql -U postgres -c "CREATE DATABASE testdb" postgres
  • Now connect from Intellij IDEA

!! Make sure Homebrew is not running Postgresql on the same port locally !!

References

(3) Postgres App

  • Download and install PostgreSQL app from https://postgresapp.com/
  • Start the app and click Initialize button
  • Then in the database shown, click on the one with name 'postgres'

(4) Enterprise DB

Installation - Client

(1) pgAdmin 4

(2) Postico

  • Mac native app
  • Not that many features as pgAdmin
  • More commerical than open source

(4) dbweaver

(3) pgWeb

Others

Create Role and Database

pgAdmin

  • Start pgAdmin
  • Create Role
    • Right-click 'Login/Group Roles' and select 'Create / Login/Group Role'
    • Enter 'Name' on the 'General' tab
    • Enter 'Password' on the 'Definition' tab
    • Select all privileges on the 'Privileges' tab
    • Click 'Save'
  • Create Database
    • Right-click 'Databases' and select 'Create / Database'
    • Enter 'Database' on the 'General' tab
    • Enter 'Owner' on the 'General' tab
    • Click 'Save'

CLI - psql tool

  • psql -U postgres - Start psql
  • CREATE ROLE "test-user" WITH LOGIN PASSWORD 'testdb';
  • CREATE DATABASE "test-db" OWNER "test-user";
  • CREATE SCHEMA "test-schema" AUTHORIZATION "test-db";

Superuser

  • CREATE ROLE user_name SUPERUSER LOGIN PASSWORD 'password';
  • or
  • ALTER USER user_name WITH SUPERUSER;

PSQL (CLI)

  • \q - quit
  • \l - list all database
  • \du - display all users & roles
  • \c (\connect) - show current database or connect to database
    • e.g. \c greetings-db
  • Tip: By default PSQL will show database name in the prompt

pg_dump and pg_restore

  • Location with brew install - /opt/homebrew/Cellar/postgresql@14/14.5_5/bin/pg_dump

SQL

Postgresql database settings

Server settings

  • To see all server settings - SELECT * FROM pg_settings;
  • Might have to reload/restart after config change*

Client settings

pgAdmin

  • SHOW datestyle; - See default datestyle. Default (ISO, MDY). Here MDY means MM-DD-YYYY. ISO is YYYY-MM-DD
  • SET datestyle = 'ISO, YMD';

Performance troubleshooting

  • pgbadger - memory leak in database connection
  • max_connections
    • ALTER SYSTEM SET max_connections = <value>
    • SHOW max_connections;
    • Change
      • psql -U <admin_user> -d <database_name>
      • ALTER SYSTEM SET max_connections = 500;
      • SELECT pg_reload_conf(); - To reload values in /opt/homebrew/var/postgresql@14/postgresql.conf file
    • Other option
SELECT name, setting
FROM pg_settings
WHERE name = 'max_connections';

Monitoring

Process

  • pg_stat_activity
    • SELECT * FROM pg_stat_activity;
    • Find process running for more than 5 minutes
SELECT pid, query, state, age(now(), query_start) AS "age"
FROM pg_stat_activity
WHERE state != 'idle' AND query_start < now() - interval '5 minute';
  • Terminate process running for more than 5 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 minutes';

//-- pg_cancel_backend(pid)
//-- state = 'idle'
//-- state != 'idle'
  • To find all blocking PIDs
select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;
  • to terminate all these PIDs replace pid in the select query with pg_terminate_backend(pid)

Special SQLs

  • UUID generation - uuid_in(overlay(overlay(md5(random()::text || ':' || random()::text) placing '4' from 13) placing to_hex(floor(random() * (11 - 8 + 1) + 8)::int)::text from 17)::cstring)
  • Timestamp - NOW()::timestamp

Cheatsheets

Versioning and upgrading