CODEWITHSUNDEEP
postgresqldatabase-backupspostgresql-extensions
Eugen Konkov
Eugen Konkov

Reputation: 25113

Why PostgreSQL extension was not dumped when created from `/docker-entrypoint-initdb.d/*` script?

I created extensions from /docker-entrypoint-initdb.d/* script like this:

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname db <<EOSQL
  CREATE EXTENSION citext;
  CREATE EXTENSION pgcrypto;
  CREATE EXTENSION hstore;
  CREATE EXTENSION unaccent;
  CREATE EXTENSION pg_trgm;
EOSQL

**Sorry, we used outdated image. Actual command is:

gosu postgres postgres --single -jE <<EOSQL
  CREATE EXTENSION citext;
  CREATE EXTENSION pgcrypto;
  CREATE EXTENSION hstore;
  CREATE EXTENSION unaccent;
  CREATE EXTENSION pg_trgm;
EOSQL

When I dump this database with pg_dumpall/pg_dump extensions does not exists in dump file and database restore fails.

pg_dump -U postgres -d userdb
pg_dumpall -U postgres

But if I connect to my database and do DROP/CREATE extension then my extensions are dumped and database successfully restored. My PostgreSQL server version is 11.12.

I found this answer and I do not understand p2. Does my CREATE EXTENSION counted as SQL script? If yes, why and how to make it non script?

Upvotes: 0

Views: 76

Answers (1)

Eugen Konkov
Eugen Konkov

Reputation: 25113

I resolution I created next 999-recreate-extensions.sh initialization script. Here I created extensions via psql:

gosu postgres pg_ctl -D "$PGDATA" -o "-c listen_addresses='' -p 5432" -w start

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname db <<EOSQL
 DROP EXTENSION IF EXISTS pg_trgm;
 DROP EXTENSION IF EXISTS unaccent;
 DROP EXTENSION IF EXISTS hstore;
 DROP EXTENSION IF EXISTS pgcrypto;
 DROP EXTENSION IF EXISTS citext;
EOSQL


psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname db -c "CREATE EXTENSION citext;"
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname db -c "CREATE EXTENSION pgcrypto;"
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname db -c "CREATE EXTENSION hstore;"
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname db -c "CREATE EXTENSION unaccent;"
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname db -c "CREATE EXTENSION pg_trgm;"

echo "EXTENSIONS Were reinstalled";

gosu postgres pg_ctl -D "$PGDATA" -m fast -w stop

After this following pg_dumpall dumps CREATE EXTENSION statements.

Upvotes: 0

Related Questions