---
toc: true
---

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

```sql
select 
    c.relnamespace::regnamespace as schema_name,
    c.relname as table_name,
    i.indexrelid::regclass as index_name,
    i.indisprimary as is_pk,
    i.indisunique as is_unique,
    i.indisvalid as is_valid, 
    i.indisready as is_ready,
    i.indisvalid as is_valid,
    i.indclass as index_class
from pg_index i
join pg_class c on c.oid = i.indrelid
where c.relname = 'tablename';
```

## Check currently active tasks/queries

```sql
SELECT * FROM pg_stat_activity WHERE NOT state = 'idle';
```

## Restore (numeric) SERIALs after a data import

```sql
DO $$
DECLARE
    rec record;
    seq_name text;
    max_id bigint;
BEGIN
    FOR rec IN
        SELECT c.table_name, c.column_name, c.column_default
        FROM information_schema.columns c
        WHERE c.table_schema = 'public'
    LOOP
        -- Attempt to get the sequence name associated with this column.
        seq_name := pg_get_serial_sequence(quote_ident(rec.table_name), rec.column_name);
		

        -- Check if we got a sequence name and if it ends with 'seq'
        IF seq_name IS NOT NULL AND seq_name LIKE '%seq' THEN
            -- If the column does not have a nextval() default, set one
           

            -- Get the current max of the column values
            EXECUTE format('SELECT max(%I) FROM %I', rec.column_name, rec.table_name) INTO max_id;

            IF max_id IS NULL THEN
                max_id := 0;
            END IF;

            RAISE NOTICE 'set nextval: %, default: %, max_id: %', seq_name, rec.column_default, max_id;

            -- Set the sequence so that the next value it generates is max_id+1
            EXECUTE format('SELECT setval(%L, %s+1, false)', seq_name, max_id);
        END IF;
    END LOOP;
END;
$$;
```

Verify next values with:

```sql
SELECT c.table_name,
               c.column_name,
               pg_get_serial_sequence(quote_ident(c.table_name), c.column_name) AS seq_name,
               nextval(pg_get_serial_sequence(quote_ident(c.table_name), c.column_name)),
               c.column_default
        FROM information_schema.columns c
        WHERE c.table_schema = 'public';
```

## Create a read-only user

```sql
CREATE USER readonly WITH PASSWORD 'your_secure_password';

-- Grant CONNECT privilege to the database
GRANT CONNECT ON DATABASE db TO readonly;

-- Grant USAGE on schema
GRANT USAGE ON SCHEMA public TO readonly;

-- Grant SELECT privilege on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Grant SELECT privilege on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
```