PostgreSQL cheat sheet

Check indexes (e.g. created concurrently) for validity/readiness

 1select 
 2    c.relnamespace::regnamespace as schema_name,
 3    c.relname as table_name,
 4    i.indexrelid::regclass as index_name,
 5    i.indisprimary as is_pk,
 6    i.indisunique as is_unique,
 7    i.indisvalid as is_valid, 
 8    i.indisready as is_ready,
 9    i.indisvalid as is_valid,
10    i.indclass as index_class
11from pg_index i
12join pg_class c on c.oid = i.indrelid
13where c.relname = 'tablename';

Check currently active tasks/queries

1SELECT * FROM pg_stat_activity WHERE NOT state = 'idle';

Restore (numeric) SERIALs after a data import

 1DO $$
 2DECLARE
 3    rec record;
 4    seq_name text;
 5    max_id bigint;
 6BEGIN
 7    FOR rec IN
 8        SELECT c.table_name, c.column_name, c.column_default
 9        FROM information_schema.columns c
10        WHERE c.table_schema = 'public'
11    LOOP
12        -- Attempt to get the sequence name associated with this column.
13        seq_name := pg_get_serial_sequence(quote_ident(rec.table_name), rec.column_name);
14		
15
16        -- Check if we got a sequence name and if it ends with 'seq'
17        IF seq_name IS NOT NULL AND seq_name LIKE '%seq' THEN
18            -- If the column does not have a nextval() default, set one
19           
20
21            -- Get the current max of the column values
22            EXECUTE format('SELECT max(%I) FROM %I', rec.column_name, rec.table_name) INTO max_id;
23
24            IF max_id IS NULL THEN
25                max_id := 0;
26            END IF;
27
28            RAISE NOTICE 'set nextval: %, default: %, max_id: %', seq_name, rec.column_default, max_id;
29
30            -- Set the sequence so that the next value it generates is max_id+1
31            EXECUTE format('SELECT setval(%L, %s+1, false)', seq_name, max_id);
32        END IF;
33    END LOOP;
34END;
35$$;

Verify next values with:

1SELECT c.table_name,
2               c.column_name,
3               pg_get_serial_sequence(quote_ident(c.table_name), c.column_name) AS seq_name,
4               nextval(pg_get_serial_sequence(quote_ident(c.table_name), c.column_name)),
5               c.column_default
6        FROM information_schema.columns c
7        WHERE c.table_schema = 'public';

Create a read-only user

 1CREATE USER readonly WITH PASSWORD 'your_secure_password';
 2
 3-- Grant CONNECT privilege to the database
 4GRANT CONNECT ON DATABASE db TO readonly;
 5
 6-- Grant USAGE on schema
 7GRANT USAGE ON SCHEMA public TO readonly;
 8
 9-- Grant SELECT privilege on all existing tables
10GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
11
12-- Grant SELECT privilege on future tables
13ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;