8000 Merge branch 'pcnc/db-upgrade-scripts-update' into pcnc/db-upgrade-sc… · supabase/postgres@4af43eb · GitHub
[go: up one dir, main page]
Skip to content

Commit 4af43eb

Browse files
pcncpcnc
committed
Merge branch 'pcnc/db-upgrade-scripts-update' into pcnc/db-upgrade-scripts
2 parents 6673b8a + 5b6732d commit 4af43eb

File tree

15 files changed

+159
-55
lines changed

15 files changed

+159
-55
lines changed

‎.github/workflows/dockerhub-release.yml

Lines chan 8000 ged: 21 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -5,8 +5,8 @@ on:
55
branches:
66
- develop
77
paths:
8-
- '.github/workflows/dockerhub-release.yml'
9-
- 'common.vars*'
8+
- ".github/workflows/dockerhub-release.yml"
9+
- "common.vars*"
1010

1111
jobs:
1212
settings:
@@ -74,10 +74,10 @@ jobs:
7474
uses: rtCamp/action-slack-notify@v2
7575
env:
7676
SLACK_WEBHOOK: ${{ secrets.SLACK_NOTIFICATIONS_WEBHOOK }}
77-
SLACK_USERNAME: 'gha-failures-notifier'
78-
SLACK_COLOR: 'danger'
79-
SLACK_MESSAGE: 'Building Postgres x86 image failed'
80-
SLACK_FOOTER: ''
77+
SLACK_USERNAME: "gha-failures-notifier"
78+
SLACK_COLOR: "danger"
79+
SLACK_MESSAGE: "Building Postgres x86 image failed"
80+
SLACK_FOOTER: ""
8181

8282
docker_arm_release:
8383
needs: settings
@@ -132,17 +132,18 @@ jobs:
132132
uses: rtCamp/action-slack-notify@v2
133133
env:
134134
SLACK_WEBHOOK: ${{ secrets.SLACK_NOTIFICATIONS_WEBHOOK }}
135-
SLACK_USERNAME: 'gha-failures-notifier'
136-
SLACK_COLOR: 'danger'
137-
SLACK_MESSAGE: 'Building Postgres arm image failed'
138-
SLACK_FOOTER: ''
135+
SLACK_USERNAME: "gha-failures-notifier"
136+
SLACK_COLOR: "danger"
137+
SLACK_MESSAGE: "Building Postgres arm image failed"
138+
SLACK_FOOTER: ""
139139

140140
merge_manifest:
141141
needs: [settings, docker_x86_release, docker_arm_release]
142142
runs-on: ubuntu-latest
143143
permissions:
144144
contents: read
145145
packages: write
146+
id-token: write
146147
steps:
147148
- uses: docker/setup-buildx-action@v2
148149

@@ -157,12 +158,16 @@ jobs:
157158
supabase/postgres@${{ needs.docker_x86_release.outputs.image_digest }} \
158159
supabase/postgres@${{ needs.docker_arm_release.outputs.image_digest }}
159160
161+
- name: configure aws credentials
162+
uses: aws-actions/configure-aws-credentials@v1
163+
with:
164+
role-to-assume: ${{ secrets.PROD_AWS_ROLE }}
165+
aws-region: us-east-1
166+
160167
- name: Login to ECR
161168
uses: docker/login-action@v2
162169
with:
163170
registry: public.ecr.aws
164-
username: ${{ secrets.PROD_ACCESS_KEY_ID }}
165-
password: ${{ secrets.PROD_SECRET_ACCESS_KEY }}
166171

167172
- name: Login to GHCR
168173
uses: docker/login-action@v2
@@ -184,7 +189,7 @@ jobs:
184189
uses: rtCamp/action-slack-notify@v2
185190
env:
186191
SLACK_WEBHOOK: ${{ secrets.SLACK_NOTIFICATIONS_WEBHOOK }}
187-
SLACK_USERNAME: 'gha-failures-notifier'
188-
SLACK_COLOR: 'danger'
189-
SLACK_MESSAGE: 'Building Postgres image failed'
190-
SLACK_FOOTER: ''
192+
SLACK_USERNAME: "gha-failures-notifier"
193+
SLACK_COLOR: "danger"
194+
SLACK_MESSAGE: "Building Postgres image failed"
195+
SLACK_FOOTER: ""

‎.github/workflows/mirror.yml

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14,12 +14,16 @@ jobs:
1414
permissions:
1515
contents: read
1616
packages: write
17+
id-token: write
1718
steps:
19+
- name: configure aws credentials
20+
uses: aws-actions/configure-aws-credentials@v1
21+
with:
22+
role-to-assume: ${{ secrets.PROD_AWS_ROLE }}
23+
aws-region: us-east-1
1824
- uses: docker/login-action@v2
1925
with:
2026
registry: public.ecr.aws
21-
username: ${{ secrets.PROD_ACCESS_KEY_ID }}
22-
password: ${{ secrets.PROD_SECRET_ACCESS_KEY }}
2327
- uses: docker/login-action@v2
2428
with:
2529
registry: ghcr.io

‎README.md

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@ Unmodified Postgres with some useful plugins. Our goal with this repo is not to
3030
| [pg_hashids](https://github.com/iCyberon/pg_hashids) | [commit](https://github.com/iCyberon/pg_hashids/commit/83398bcbb616aac2970f5e77d93a3200f0f28e74) | Generate unique identifiers from numbers. |
3131
| [pgsodium](https://github.com/michelp/pgsodium) | [3.1.0](https://github.com/michelp/pgsodium/releases/tag/2.0.0) | Modern encryption API using libsodium. |
3232
| [pg_stat_monitor](https://github.com/percona/pg_stat_monitor) | [1.0.1](https://github.com/percona/pg_stat_monitor/releases/tag/1.0.1) | Query Performance Monitoring Tool for PostgreSQL
33+
| [pgvector](https://github.com/pgvector/pgvector) | [v0.4.0](https://github.com/pgvector/pgvector/releases/tag/v0.4.0) | Open-source vector similarity search for Postgres
3334

3435

3536
Can't find your favorite extension? Suggest for it to be added into future releases [here](https://github.com/supabase/supabase/discussions/679)!

‎ansible/files/admin_api_scripts/pg_upgrade_complete.sh

Lines changed: 27 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -13,11 +13,19 @@ EXTENSIONS_TO_REENABLE=(
1313
"pg_graphql"
1414
)
1515

16-
set -euo pipefail
16+
set -eEuo pipefail
1717

1818
run_sql() {
19-
STATEMENT=$1
20-
psql -h localhost -U supabase_admin -d postgres -c "$STATEMENT"
19+
psql -h localhost -U supabase_admin -d postgres "$@"
20+
}
21+
22+
cleanup() {
23+
UPGRADE_STATUS=${1:-"failed"}
24+
EXIT_CODE=${?:-0}
25+
26+
echo "${UPGRADE_STATUS}" > /tmp/pg-upgrade-status
27+
28+
exit $EXIT_CODE
2129
}
2230

2331
cleanup() {
@@ -30,31 +38,36 @@ cleanup() {
3038
}
3139

3240
function complete_pg_upgrade {
41+
if [ -f /tmp/pg-upgrade-status ]; then
42+
echo "Upgrade job already started. Bailing."
43+
exit 0
44+
fi
45+
3346
echo "running" > /tmp/pg-upgrade-status
3447

3548
mount -a -v
3649

3750
# copying custom configurations
3851
cp -R /data/conf/* /etc/postgresql-custom/
52+
chown -R postgres:postgres /var/lib/postgresql/data
53+
chown -R postgres:postgres /data/pgdata
3954

4055
service postgresql start
4156

4257
for EXTENSION in "${EXTENSIONS_TO_REENABLE[@]}"; do
43-
run_sql "CREATE EXTENSION IF NOT EXISTS ${EXTENSION} CASCADE;"
58+
run_sql -c "CREATE EXTENSION IF NOT EXISTS ${EXTENSION} CASCADE;"
4459
done
4560

61+
if [ -d /data/sql ]; then
62+
for FILE in /data/sql/*.sql; do
63+
run_sql -f $FILE
64+
done
65+
fi
66+
4667
sleep 5
4768
service postgresql restart
4869

49-
if [[ $(systemctl is-active gotrue) == "inactive" ]]; then
50-
echo "starting gotrue"
51-
systemctl start --no-block gotrue || true
52-
fi
53-
54-
if [[ $(systemctl is-active postgrest) == "inactive" ]]; then
55-
echo "starting postgrest"
56-
systemctl start --no-block postgrest || true
57-
fi
70+
start_vacuum_analyze
5871

5972
echo "Upgrade job completed"
6073
}
@@ -66,5 +79,4 @@ function start_vacuum_analyze {
6679

6780
trap cleanup ERR
6881

69-
complete_pg_upgrade >>/var/log/pg-upgrade-complete.log 2>&1
70-
start_vacuum_analyze >>/var/log/pg-upgrade-complete.log 2>&1 &
82+complete_pg_upgrade >>/var/log/pg-upgrade-complete.log 2>&1 &

‎ansible/files/admin_api_scripts/pg_upgrade_initiate.sh

Lines changed: 16 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -71,8 +71,6 @@ cleanup() {
7171

7272
run_sql " F438 ALTER USER postgres WITH NOSUPERUSER;"
7373

74-
start_services
75-
7674
umount $MOUNT_POINT
7775
echo "${UPGRADE_STATUS}" > /tmp/pg-upgrade-status
7876

@@ -82,12 +80,14 @@ cleanup() {
8280
function initiate_upgrade {
8381
echo "running" > /tmp/pg-upgrade-status
8482

85-
shutdown_services
86-
8783
# awk NF==3 prints lines with exactly 3 fields, which are the block devices currently not mounted anywhere
8884
# excluding nvme0 since it is the root disk
8985
BLOCK_DEVICE=$(lsblk -dprno name,size,mountpoint,type | grep "disk" | grep -v "nvme0" | awk 'NF==3 { print $1; }')
9086

87+
if [ -x "$(command -v blockdev)" ]; then
88+
blockdev --rereadpt "$BLOCK_DEVICE"
89+
fi
90+
9191
mkdir -p "$MOUNT_POINT"
9292
mount "$BLOCK_DEVICE" "$MOUNT_POINT"
9393
resize2fs "$BLOCK_DEVICE"
@@ -129,7 +129,9 @@ function initiate_upgrade {
129129
WORKERS=$(nproc | awk '{ print ($1 == 1 ? 1 : $1 - 1) }')
130130

131131
# upgrade job outputs a log in the cwd; needs write permissions
132-
cd /tmp
132+
mkdir -p /tmp/pg_upgrade
133+
chown -R postgres:postgres /tmp/pg_upgrade
134+
cd /tmp/pg_upgrade
133135

134136
UPGRADE_COMMAND=$(cat <<EOF
135137
time ${PGBINNEW}/pg_upgrade \
@@ -148,15 +150,22 @@ EOF
148150
ln -s /tmp/pg_upgrade_bin/15/share /var/lib/postgresql
149151

150152
if [ ! -L /var/lib/postgresql.bak/data ]; then
153+
if [ -L /var/lib/postgresql/data ]; then
154+
rm /var/lib/postgresql/data
155+
fi
151156
ln -s /var/lib/postgresql.bak/data /var/lib/postgresql/data
152157
fi
153158

154159
systemctl stop postgresql
155160
su -c "$UPGRADE_COMMAND" -s $SHELL postgres
156161

157162
# copying custom configurations
158-
mkdir -p $MOUNT_POINT/conf
159-
cp -R /etc/postgresql-custom/* $MOUNT_POINT/conf/
163+
mkdir -p "$MOUNT_POINT/conf"
164+
cp -R /etc/postgresql-custom/* "$MOUNT_POINT/conf/"
165+
166+
# copy sql files generated by pg_upgrade
167+
mkdir -p "$MOUNT_POINT/sql"
168+
cp /tmp/pg_upgrade/*.sql "$MOUNT_POINT/sql/" || true
160169

161170
cleanup "complete"
162171
}

‎ansible/files/admin_api_scripts/pg_upgrade_prepare.sh

Lines changed: 0 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -9,16 +9,6 @@
99

1010
set -euo pipefail
1111

12-
if [[ $(systemctl is-active gotrue) == "active" ]]; then
13-
echo "stopping gotrue"
14-
systemctl stop gotrue || true
15-
fi
16-
17-
if [[ $(systemctl is-active postgrest) == "active" ]]; then
18-
echo "stopping postgrest"
19-
systemctl stop postgrest || true
20-
fi
21-
2212
systemctl stop postgresql
2313

2414
cp /etc/postgresql-custom/pgsodium_root.key /data/pgsodium_root.key

‎ansible/files/adminapi.sudoers.conf

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@ Cmnd_Alias PGBOUNCER = /bin/systemctl start pgbouncer.service, /bin/systemctl st
1616
%adminapi ALL= NOPASSWD: /root/pg_upgrade_check.sh
1717
%adminapi ALL= NOPASSWD: /root/pg_upgrade_pgsodium_getkey.sh
1818
%adminapi ALL= NOPASSWD: /usr/bin/systemctl daemon-reload
19+
%adminapi ALL= NOPASSWD: /usr/bin/systemctl reload postgresql.service
1920
%adminapi ALL= NOPASSWD: /usr/bin/systemctl restart postgresql.service
2021
%adminapi ALL= NOPASSWD: /usr/bin/systemctl restart adminapi.service
2122
%adminapi ALL= NOPASSWD: /bin/systemctl daemon-reload

‎ansible/files/logind.conf

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
[Login]
2+
RemoveIPC=no

‎ansible/files/postgresql_config/supautils.conf.j2

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
supautils.placeholders = 'response.headers'
22
supautils.placeholders_disallowed_values = '"content-type"'
3-
# full list: address_standardizer, address_standardizer_data_us, adminpack, amcheck, autoinc, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, file_fdw, fuzzystrmatch, hstore, http, insert_username, intagg, intarray, isn, lo, ltree, moddatetime, old_snapshot, pageinspect, pg_buffercache, pg_cron, pg_freespacemap, pg_graphql, pg_hashids, pg_jsonschema, pg_net, pg_prewarm, pg_stat_monitor, pg_stat_statements, pg_surgery, pg_trgm, pg_visibility, pg_walinspect, pgaudit, pgcrypto, pgjwt, pgroonga, pgroonga_database, pgrouting, pgrowlocks, pgsodium, pgstattuple, pgtap, plcoffee, pljava, plls, plpgsql, plpgsql_check, plv8, postgis, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, postgis_topology, postgres_fdw, refint, rum, seg, sslinfo, supabase_vault, supautils, tablefunc, tcn, timescaledb, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, wrappers, xml2
3+
# full list: address_standardizer, address_standardizer_data_us, adminpack, amcheck, autoinc, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, file_fdw, fuzzystrmatch, hstore, http, insert_username, intagg, intarray, isn, lo, ltree, moddatetime, old_snapshot, pageinspect, pg_buffercache, pg_cron, pg_freespacemap, pg_graphql, pg_hashids, pg_jsonschema, pg_net, pg_prewarm, pg_stat_monitor, pg_stat_statements, pg_surgery, pg_trgm, pg_visibility, pg_walinspect, pgaudit, pgcrypto, pgjwt, pgroonga, pgroonga_database, pgrouting, pgrowlocks, pgsodium, pgstattuple, pgtap, pgvector, plcoffee, pljava, plls, plpgsql, plpgsql_check, plv8, postgis, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, postgis_topology, postgres_fdw, refint, rum, seg, sslinfo, supabase_vault, supautils, tablefunc, tcn, timescaledb, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, wrappers, xml2
44
# omitted because may be unsafe: adminpack, amcheck, file_fdw, lo, old_snapshot, pageinspect, pg_buffercache, pg_freespacemap, pg_prewarm, pg_surgery, pg_visibility, pgstattuple
55
# omitted because deprecated: intagg, xml2
6-
supautils.privileged_extensions = 'address_standardizer, address_standardizer_data_us, autoinc, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, fuzzystrmatch, hstore, http, insert_username, intarray, isn, ltree, moddatetime, pg_cron, pg_graphql, pg_hashids, pg_jsonschema, pg_net, pg_stat_monitor, pg_stat_statements, pg_trgm, pg_walinspect, pgaudit, pgcrypto, pgjwt, pgroonga, pgroonga_database, pgrouting, pgrowlocks, pgsodium, pgtap, plcoffee, pljava, plls, plpgsql, plpgsql_check, plv8, postgis, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, postgis_topology, postgres_fdw, refint, rum, seg, sslinfo, supabase_vault, supautils, tablefunc, tcn, timescaledb, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, wrappers'
6+
supautils.privileged_extensions = 'address_standardizer, address_standardizer_data_us, autoinc, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, fuzzystrmatch, hstore, http, insert_username, intarray, isn, ltree, moddatetime, pg_cron, pg_graphql, pg_hashids, pg_jsonschema, pg_net, pg_stat_monitor, pg_stat_statements, pg_trgm, pg_walinspect, pgaudit, pgcrypto, pgjwt, pgroonga, pgroonga_database, pgrouting, pgrowlocks, pgsodium, pgtap, pgvector, plcoffee, pljava, plls, plpgsql, plpgsql_check, plv8, postgis, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, postgis_topology, postgres_fdw, refint, rum, seg, sslinfo, supabase_vault, supautils, tablefunc, tcn, timescaledb, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, wrappers'
77
supautils.privileged_extensions_custom_scripts_path = '/etc/postgresql-custom/extension-custom-scripts'
88
supautils.privileged_extensions_superuser = 'supabase_admin'
99
supautils.privileged_role = 'postgres'

‎ansible/tasks/postgres-extensions/27-pg-repack.yml

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
# pg-repack
2+
- name: pg_repack - download latest release
3+
get_url:
4+
url: "https://github.com/reorg/pg_repack/releases/tag/ver_{{pg_repack_release}}.tar.gz"
5+
dest: /tmp/pg-repack-{{ pg_repack_release }}.tar.gz
6+
checksum: "{{ pg_repack_release_checksum }}"
7+
timeout: 60
8+
9+
- name: pg_repack - unpack archive
10+
unarchive:
11+
remote_src: yes
12+
src: /tmp/pg_repack-{{ pg_repack_release }}.tar.gz
13+
dest: /tmp
14+
become: yes
15+
16+
- name: pg_repack - build
17+
make:
18+
chdir: /tmp/pg_repack-{{ pg_repack_release }}
19+
params:
20+
USE_PGXS: 1
21+
become: yes
22+
23+
- name: pg_repack - install
24+
make:
25+
chdir: /tmp/pg_repack-{{ pg_repack_release }}
26+
target: install
27+
params:
28+
USE_PGXS: 1
29+
become: yes
30+
31+
- name: rum - cleanup
32+
file:
33+
state: absent
34+
path: /tmp/pg_repack-{{ pg_repack_release }}

‎ansible/tasks/postgres-extensions/28-pgvector.yml

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
# pgvector
2+
- name: pgvector - download latest release
3+
git:
4+
repo: https://github.com/pgvector/pgvector.git
5+
dest: /tmp/pgvector
6+
version: '{{ pgvector_release }}'
7+
become: yes
8+
9+
- name: pgvector - build
10+
make:
11+
chdir: /tmp/pgvector
12+
become: yes
13+
14+
- name: pgvector - install
15+
make:
16+
chdir: /tmp/pgvector
17+
target: install
18+
become: yes
19+
20+
- name: pgvector - cleanup
21+
file:
22+
state: absent
23+
path: /tmp/pgvector

‎ansible/tasks/setup-extensions.yml

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -75,8 +75,13 @@
7575

7676
- name: Install hypopg
7777
import_tasks: tasks/postgres-extensions/26-hypopg.yml
78+
79+
- name: Install pg_repack
80+
import_tasks: tasks/postgres-extensions/27-pg_repack.yml
81+
82+
- name: Install pgvector
83+
import_tasks: tasks/postgres-extensions/28-pgvector.yml
7884

7985
- name: Verify async task status
8086
import_tasks: tasks/postgres-extensions/99-finish_async_tasks.yml
8187
when: async_mode
82-

‎ansible/tasks/setup-postgres.yml

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,8 @@
1515
- uuid-dev
1616
- xsltproc
1717
- ssl-cert
18+
- liblz4-dev
19+
- libicu-dev
1820

1921
- name: Download LLVM & Clang
2022
apt:
@@ -67,7 +69,7 @@
6769

6870
- name: Postgres - configure
6971
shell:
70-
cmd: CFLAGS='{{ cflags }}' LLVM_CONFIG=/usr/bin/llvm-config-11 CLANG=/usr/bin/clang-11 ./configure --with-llvm --with-openssl --with-systemd --with-uuid=e2fs --with-libxml --exec-prefix=/usr/lib/postgresql --datarootdir=/var/lib/postgresql
72+
cmd: CFLAGS='{{ cflags }}' LLVM_CONFIG=/usr/bin/llvm-config-11 CLANG=/usr/bin/clang-11 ./configure --with-llvm --with-openssl --with-systemd --with-uuid=e2fs --with-libxml --with-icu --with-lz4 --exec-prefix=/usr/lib/postgresql --datarootdir=/var/lib/postgresql
7173
chdir: /tmp/postgresql-{{ postgresql_release }}
7274

7375
- name: Postgres - build
@@ -117,6 +119,7 @@
117119
group: postgres
118120
mode: 0775
119121
with_items:
122+
- '/etc/postgresql'
120123
- '/etc/postgresql-custom'
121124

122125
- name: create placeholder config files

0 commit comments

Comments
 (0)
0