Solid-State Society

Tag: PostgreSQL

  • Logto Database Migration

    Create database and role BEFORE db restoration

    docker exec -i docker-logto-db-1 psql -U postgres <<EOF
-- Create the database
CREATE DATABASE logto;

-- Create the missing role
CREATE ROLE logto_tenant_logto WITH LOGIN PASSWORD 'your_secure_password';

-- Grant connection privilege
GRANT CONNECT ON DATABASE logto TO logto_tenant_logto;

-- Connect to logto database for schema permissions
\c logto

-- Grant schema permissions
GRANT USAGE ON SCHEMA public TO logto_tenant_logto;
GRANT CREATE ON SCHEMA public TO logto_tenant_logto;

-- Grant default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO logto_tenant_logto;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO logto_tenant_logto;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO logto_tenant_logto;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TYPES TO logto_tenant_logto;
EOF

    Then restore the backup

    gunzip < logto.sql.gz | docker exec -i docker-logto-db-1 psql -d logto -U postgres

    This can help you avoid ERROR: role "logto_tenant_logto" does not exist errors

  • Self-hosted Sentry Postgres Database Cleanup

    The official documentation is event wrong, when you execute the following command:

    docker compose run --rm -T postgres bash -c "apt update && apt install -y --no-install-recommends postgresql-14-repack && su postgres -c 'pg_repack -E info -t nodestore_node'"

    You will get the following error:

    Removing obsolete dictionary files:
ERROR: could not connect to database: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?

    This issue has been tracked on GitHub but does not have a solution. Here’s my solution:

    docker compose -f docker-compose.yml -f docker-compose.override.yml --env-file /srv/git/sentry/.env.custom exec -T postgres bash -c "
  apt update && apt install -y --no-install-recommends postgresql-14-repack &&
  su postgres -c 'psql -c \"CREATE EXTENSION IF NOT EXISTS pg_repack;\"' &&
  su postgres -c 'pg_repack -E info -t nodestore_node'
"