Ellie Huxtable█ ~/notes/postgresql

PostgreSQL

Sep 01, 2023 [postgresql, infra]

Some postgres snippets! Just a reference page for the things I forget a lot.

  • pg_ctl init -D path - init new database + config at path
  • pg_hba.conf - configure host based auth
  • pg_ident.conf - map system users to database users
  • postgresql.conf - all the other config changes

Learnings

  • It's faster to create a table with no index, copy data in, then add indices
  • Using random_page_cost=1.1 for SSD backed databases works much better

Snippets

Commands

  • \l list databases
  • \c dbname connect to database as current user

Create table as copy of another

create table new_table as table old_table;

Note: this will copy all data, but no indices or constraints

For no data

create table new_table as table old_table with no data;

If you'd like to query/filter it:

create table new_table as (select * from old_table where some_condition);

Check for waiting locks

select relation::regclass, * from pg_locks where not granted;

Get database size

SELECT pg_size_pretty(pg_database_size('database name'));

Get table size

SELECT pg_size_pretty(pg_relation_size('records'));

Monitoring replication slots

SELECT * FROM pg_replication_slots;

Monitoring replication lag

SELECT extract(epoch from now() - pg_last_xact_replay_timestamp()) AS replica_lag

Dump database schema only

pg_dump --schema-only databasename

Useful tools