Skip to content

Instantly share code, notes, and snippets.

@BradleyJohnson
Last active November 5, 2020 20:35
Show Gist options
  • Save BradleyJohnson/ff06419121ac146ba19994ceafb99902 to your computer and use it in GitHub Desktop.
Save BradleyJohnson/ff06419121ac146ba19994ceafb99902 to your computer and use it in GitHub Desktop.
stock xenial pg12 docker image and reproduce collation error
# Start stock xenial
> docker run -it ubuntu:xenial bash
# Prepare image
apt-get update \
&& apt-get install vim software-properties-common wget apt-transport-https
add-apt-repository -y "deb https://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" \
&& wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update \
&& apt-get install -y --no-install-recommends postgresql-common \
&& echo 'create_main_cluster = false' >> /etc/postgresql-common/createcluster.conf \
&& apt-get install -y --no-install-recommends \
postgresql-12 \
postgresql-12-dbg \
postgresql-12-pgextwlist \
postgresql-client-12 \
postgresql-contrib-12
# Update locale and make dir/files
locale-gen en_US.UTF-8 && update-locale;
mkdir /database && chown postgres /database;
touch /logfile && chown postgres /logfile;
# initdb and setup
su postgres
env \
LC_COLLATE=en_US.UTF-8 \
LC_CTYPE=en_US.UTF-8 \
LC_MESSAGES=en_US.UTF-8 \
LC_MONETARY=en_US.UTF-8 \
LC_NUMERIC=en_US.UTF-8 \
LC_TIME=en_US.UTF-8 \
/usr/lib/postgresql/12/bin/initdb -D /database -E UTF8 -U postgres
mkdir -p /database/pgextwlist/pg_stat_statements && touch /database/pgextwlist/pg_stat_statements/after-create.sql;
echo "GRANT EXECUTE ON FUNCTION pg_stat_statements_reset() TO test_user;" >> /database/pgextwlist/pg_stat_statements/after-create.sql;
#Update configuration to use pgextwlist
cat <<EOF >> /database/postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
local_preload_libraries = pgextwlist
extwlist.extensions = 'pg_stat_statements'
extwlist.custom_path = '/database/pgextwlist'
EOF
# Start process, create user/db
/usr/lib/postgresql/12/bin/pg_ctl -D /database -l /logfile start
psql -U postgres << EOF
CREATE ROLE test_user;
ALTER ROLE test_user WITH LOGIN PASSWORD 'test_pwd' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE DATABASE test_db OWNER test_user;
GRANT CONNECT ON DATABASE test_db TO test_user;
GRANT ALL ON DATABASE test_db TO test_user;
GRANT ALL ON SCHEMA public TO test_user;
EOF
# Trigger error
psql -U test_user -d test_db -c 'CREATE EXTENSION pg_stat_statements WITH SCHEMA public'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment