Postgres
- https://explain.depesz.com/ — Tuning
Start/Stop on macos
sudo -i su - postgres cd /Library/PostgreSQL/9.6/bin/ ./pg_ctl -D /Library/PostgreSQL/9.6/data stop -s -m fast # stop ./pg_ctl -D /Library/PostgreSQL/9.6/data -l /Library/PostgreSQL/9.6/data/pg_log/postgresql-server.log start launchctl remove com.edb.launchd.postgresql-9.6
Backup/Restore
Dump/Restore
pg_dump - pg_restore
# --schema(-n) --table(-t) --schema-only(-s) --blobs(-b) # --data-only(-a) --jobs(-j) --no-privileges(-x) --compress(-Z9) # --no-owner(-O) pg_dump -d $DBURL -f out.sql pg_dump -d $DBURL -O -x -b -Fc -Z9 -n schemaname > $PGDUMPFILE pg_restore -l db.dump #list content. only -Fc pg_restore -d $DBURL --clean --create --jobs 4 --no-owner db.dump psql -d $DB1HORIZON < practice-from-db-1-grandedemo.sql pg_dump -d $DB1GRANDE -Oxb -Fc -Z9 -n grande -j4 > grande-2.dump pg_restore -d $DB1HORIZON -Ocxj4 grande-2.dump pg_dump -d $DB1GRANDE -Os -n grande > grande-schema-2.sql pg_dump -d $DB1GRANDE -Oxba -Fc -Z9 -n grande > grande-data-1.dump psql -d $DB1HORIZON < grande-schema-2.sql pg_restore -d $DB1HORIZON -aOx --disable-triggers grande-2.dump pg_restore --disable-triggers --data-only --dbname=your_database your_dump_file
restore local
export PGPASSWORD= echo 'drop database nmpf;' \ |psql -h localhost -U postgres pg_restore \ -h localhost -U postgres \ --clean --create -d postgres ./backup.dump dc exec postgres bash pg_restore -U postgres --clean --create -d postgres ./backups/a.dump # sql dump. DANGER: overwrites db that is inside the dump file. psql -h localhost -U postgres -d nmpf < defs-table.dump pg_restore --create -d postgres ./2024-02-17-prod.dump /sbin/runuser -l postgres -c '\ /usr/bin/pg_dump --blobs --format=c --compress=9 -U postgres nmpf \ > /tmp/$(date '+%Y-%m-%d-%H%M%S')-prod.dump'
pg_dump/restore
# from prod pg_dump --blobs --format=c --compress=9 -U postgres nmpf > /backups/nmpf.dump # one table pg_dump --data-only --table=tablename sourcedb > onetable.pg psql destdb < onetable.pg # extract one table from full dump pg_restore --data-only --table=tablename fulldump.pg > onetable.pg /sbin/runuser -l postgres -c \ '/usr/bin/pg_dump --data-only --table=evaluation_definitions \ --format=c --compress=9 -U postgres nmpf \ > /tmp/$(date '+%Y-%m-%d-%H%M%S')-the-forms.dump' /sbin/runuser -l postgres -c \ '/usr/bin/pg_restore --data-only --table=evaluation_definitions \ -d nmpf /tmp/2021-05-11-200938-the-forms.dump' # ------------- # extract form defs table from a dump pg_restore --data-only \ --table=evaluation_definitions \ fulldump.pg \ -f defs-table.dump export PGPASSWORD= psql -h localhost -U postgres -d nmpf truncate table evaluation_definitions; psql -h localhost -U postgres -d nmpf -f defs-table.dump # output is raw SQL pg_dump --dbname=nmpf --if-exists --clean --create # or -Fp
ChatGPT dump/restore single table
pg_dump -U username -h host -d database_name \ --table=table_name -Fc -f table_backup.dump # -Fc Outputs the dump in custom format # -Fp for plain text if preferred # plain psql \ -U username -h host -d database_name \ -f table_backup.sql # custom pg_restore \ -U username -h host -d database_name \ --table=table_name table_backup.dump # with schema --table=schema_name.table_name
Just the schema
# read just schema from dump file pg_restore -sf - nmpf.dump |less
dump/restore to sql to search a db
- dump to file ie: db.dump
- extract sql to file
pg_restore -a -f db.sql db.dump
- search the file
egrep '^COPY|somestring' db.sql > search.log
Backup Script
#!/bin/bash set -vx TS=$(date '+%Y-%m-%d-%H%M%S') FNAME="/backups/${TS}-${1}.dump" docker exec -it \ postgres bash -c "pg_dump \ --blobs --format=c --compress=9 \ -U docker $1 \ > $FNAME" aws s3 sync pg-backups/ s3://xx-dev-backups/x-dev/
Restore Script
# restore cp efs/backups/database/... ./backups/ dc exec postgres bash cd /backups/ psql -U postgres # may have to kill connections with select pg_terminate_backend(pid) from pg_stat_activity where datname='nmpf'; alter database nmpf rename to nmpf20250610; create database nmpf; pg_restore -h localhost -U postgres --clean --create -d nmpf /backups/2024-01-04-nmpf.dump exit ./console-run.sh doctrine:migrations:migrate # console #!/bin/bash cd $HOME/deploy # docker-compose run --rm -u www-data backend /app/bin/console "$@" docker-compose run --rm backend /app/bin/console "$@" # kill connections and rename docker run --rm -it --name postgres_client postgres:16.3 \ psql --dbname "postgres://postgres:xxx@hostname:5432/postgres" \ -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'old_database_name' AND pid <> pg_backend_pid();" \ -c "ALTER DATABASE old_database_name RENAME TO new_database_name;"
Terminate connections
/sbin/runuser -l postgres -c psql -- list connections SELECT * FROM pg_stat_activity; -- kill connections select pg_terminate_backend(pid) from pg_stat_activity where datname='nmpf'; -- all but DataGrip SELECT * FROM pg_stat_activity WHERE state = 'active' and not application_name ~ 'DataGrip'; SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active' and not application_name ~ 'DataGrip';
Query data
Explain
psql -XqAt -f explain.sql > analyze.json
Users
User Management
AWS Managing Postgres
-- Revoke privileges from 'public' role REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE ALL ON DATABASE mydatabase FROM PUBLIC; -- Read-only role CREATE ROLE readonly; GRANT CONNECT ON DATABASE mydatabase TO readonly; GRANT USAGE ON SCHEMA myschema TO readonly; GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly; -- Read/write role CREATE ROLE readwrite; GRANT CONNECT ON DATABASE mydatabase TO readwrite; GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite; ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite; GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite; ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite; -- Users creation CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd'; CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd'; CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd'; CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd'; -- Grant privileges to users GRANT readonly TO reporting_user1; GRANT readonly TO reporting_user2; GRANT readwrite TO app_user1; GRANT readwrite TO app_user2;
Show Privs for User
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'usrhorizon'; SELECT * FROM pg_tables WHERE tableowner = 'usrhorizon'; SELECT r.usename AS grantor, e.usename AS grantee, nspname, privilege_type, is_grantable FROM pg_namespace JOIN LATERAL ( SELECT * FROM aclexplode(nspacl) AS x ) a ON true JOIN pg_user e ON a.grantee = e.usesysid JOIN pg_user r ON a.grantor = r.usesysid WHERE e.usename = 'usrhorizon'; # list perms SELECT r.rolname, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof FROM pg_catalog.pg_roles r WHERE r.rolname NOT IN ('pg_signal_backend','rds_iam', 'rds_replication','rds_superuser', 'rdsadmin','rdsrepladmin') ORDER BY 1;
Change User Password
dc exec -u postgres bash psql -U postgres ALTER USER postgres WITH PASSWORD 'new_password';
Run as postgres user
/sbin/runuser -l postgres -c 'psql'
Connecting
Using Service File
export PGSERVICEFILE=.pgservice [p1] user=postgres password=xxx host=dbhost.com port=5432 dbname=postgres psql -d 'service=p1 dbname=other'
URL
# url postgresql://username:password@hostname:port/database export DBURL='postgres://user:xpass@host.com:5432/dbname' psql -d $DBURL SELECT version();
Reload Configuration (edit pg_hba.conf)
vi /etc/postgresql/10/main/pg_hba.conf hostssl all all 172.31.15.114/32 md5 # wireguard on bastion su - postgres psql # in psql ... SELECT pg_reload_conf(); # cli pg_ctl reload
Unused Indexes
SELECT s.schemaname, s.relname AS tablename, s.indexrelname AS indexname, pg_relation_size(s.indexrelid) AS index_size FROM pg_catalog.pg_stat_user_indexes s JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid WHERE s.idx_scan = 0 -- has never been scanned AND 0 <>ALL (i.indkey) -- no index column is an expression AND NOT i.indisunique -- is not a UNIQUE index AND NOT EXISTS -- does not enforce a constraint (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conindid = s.indexrelid) ORDER BY pg_relation_size(s.indexrelid) DESC;
Hung transactions
htopsearch with F4 type inpostgres
- Check for processes in transaction and idle
- Hightlight that row
- Press
kto send signal 15
- All updates and such should complete
Need to learn clustering
Change Data Capture (CDC)
Python Stored Procedures
Quoting strings
# From William docker=# create table wre (id int, col text); CREATE TABLE docker=# insert into wre values (1, 'someother stuff here\\/mcap\\/deadline') docker-# ; INSERT 0 1 docker=# insert into wre values (1, 'someother stuff here\/mcap\/deadline') ; INSERT 0 1 docker=# select * from wre; id | col ----+---------------------------------------- 1 | someother stuff here\\/mcap\\/deadline 1 | someother stuff here\/mcap\/deadline (2 rows) docker=# select * from wre where col like '%\\/mcap\\/deadline%'; id | col ----+-------------------------------------- 1 | someother stuff here\/mcap\/deadline (1 row) docker=# select * from wre where col like '%\\\\/mcap\\\\/deadline%'; id | col ----+---------------------------------------- 1 | someother stuff here\\/mcap\\/deadline docker=# select * from wre where col like '%\/mcap\/deadline%' escape '^'; id | col ----+-------------------------------------- 1 | someother stuff here\/mcap\/deadline (1 row)
Import from SqlServer
Training
Tuning
Slow Query Log
Query durations
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
- log_min_duration
- autovacuum settings
- pg bouncer
- pg pool
Install client for RDS
sudo apt install postgresql-client-common sudo apt install postgresql-client-16 psql postgresql://xx_admin:${PWD}@xx-platform-db.xxx.us-west-2.rds.amazonaws.com:5432/xx_prod
PostGIS Extension
\c postgres SELECT * FROM pg_available_extensions WHERE name LIKE 'postgis%'; CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology; -- optional CREATE EXTENSION postgis_raster; -- optional (requires parameter group change)