8000 Sam/oriole17 (#1298) · cepro/postgres@54d80f6 · GitHub
[go: up one dir, main page]
Skip to content

Commit 54d80f6

Browse files
samroseolirice
samrose
and
olirice
authored
Sam/oriole17 (supabase#1298)
* feat: build and flake check of pg 16.3 with exts/wrappers * pg_partman test 15/16 compat * merge sql interface test * tests: build test and cache both versions * chore: run checks individually * pg 15 and 16 packer/ansible/ghactions (supabase#1268) * fix: reformat ec2 cleanup commands (supabase#1267) Co-authored-by: Sam Rose <samuel@supabase.io> * feat: build and flake check of pg 16.3 with exts/wrappers * pg_partman test 15/16 compat * merge sql interface test * tests: build test and cache both versions * chore: run checks individually * feat: realease 15 and 16 to staging * chore: update versions * chore: make yq available * chore: run yq from nix * chore: more setup for staging AMI * fix: yq usage * chore: shell vars * fix: When --init none is used, only users who can elevate to sudo privileges can run Nix * fix: no -i * fix: quote correction * fix: newline extra quotes * fix: no need for pg major version on packer * fix: postgresql_major * fix: ql * fix: no ansible args in stage to invocation * fix: unique val * fix: adjustments to build scripts * chore: env var handling * fix: bump to build * chore: set up more required vars * chore: bump var * feat: pg 16 debug symbols * feat: matrix pg versions build on testinfra * feat: matrix on Test Database * chore: running nix in the right context * feat: just use existing Dockerfile + pg version * chore: refer to var * fix: read name without including quotes * chore: try format function * fix: strip quotes from version number * chore: env var * fix: pg client * fix * fix: try to use psql from our own corresponding pkg * fix: try psql from ppa * fix: dbmate per pg version * build dbmate and then install client * fix: account for architecture * chore: limit changes detection migrations/schema.sql * missing docker compose call * ore: drop tests while investigating * test: try on pg15 only * chore: schema needs update * chore: now run on all versions in matrix * test: trying a version of schema per major pg version as there are type diffs --------- Co-authored-by: Sam Rose <samuel@supabase.io> Co-authored-by: Oliver Rice <github@oliverrice.com> * chore: rm dead code * chore: resolve versions from ansible/vars.yml instead of packer file create a matrix build for 15 16 (or how ever many versions there are) * feat: WIP orioledb 16 * feat: intro into CI building psql16 bundle oriole version * test: working through build compat for oriole * working orioledb build * fix: update value * chore: cut staging only release for oriole16 * narrow down to just oriole staging ami here * fix: make sure name lines up with gh action convention * fix: get correct naming convention in here too for oriole bundle * chore: bump to cut release * fix: make sure src and debug builds available for oriole too * chore: bump to staging release * fix: when oriole16 rm timescale from supautils conf * fix: also remove from postgresql conf * fix: find all instances and remove in this condition for tiemscaledb * fix: maybe broke pgsodium with too many spaces * fix: create a symlink for missing path * fix: try a more universal way to deactivate pgsodium at this stage * chore: when oriole16 rm timescale plv8 postgis pgvector pgrouting * fix: more handling of oriole16 differences * chore more oriole16 handling * fix: refine sed regex * fix: also cond rm actual files * fix: correct dir * chore: bump to release * feat: parse and handle oropledb-16 in start-server * feat: start-client for oriole16 * wip for orioledb push to build on linux * fix: running server * chore: consolidate nix code that handles building of postgres versions * fix: update name for bundle * chore: small cleanup * chore: bump version release * chore: cleanup names * chore: correct names * fix: var name * fix: one more var name * chore: bump to release * fix: reformatting metadata for clikchouse dep as git was not able to fetch * feat: wip refactor to pg 17 for orioledb * feat: working orioledb-17 * fix: increase role duration to avoid expiration * fix: correct version * fix: clickhouse needs git in build inputs * fix: install and then remove git for clickhouse dep * fix: correct rules for version * fix: db_user_namespace was deprecated starting in pg 16.4 and higher * apply fix for wrappers build, deactivate ext in unit test * chore: deactivate more tests due to deactivated extensions * fix: also remove from sql file * fix: regex * fix: also remove ref here * fix: graphql_public schema too * chore: correct dir * chore: staging release * feat: re-introduce wal2json rum and pgvector * fix: also restore pgvector in start-server * chore: bump staging release * feat: oriole specific default settings * feat: use icu for locales only if building orioledb * fix: need var in stage 1 too * fix: settings must be in db init * fix: ansible args * fix: PostgreSQL uses ICU for all locale-related operations, so we don't need to specify the collation and character type settings separately. * chore: add a migration for orioledb activation by default * feat: turn on oriole if oriole ext exists * test: conditional on test for orioledb * fix: salt changed repo and method for adding apt package * fix: udpate salt minion setup * fix: silent skip if oriole not in the install * test: check if oriole is available and if so then enable * chore: build and cache src and debug pkgs as well * fix: tmp supress build of src and debug * chore: tmp disable src and debug * fix: activate oriole first in the oriole context * fix: pgroonga build on macos * fix: restore flake check for now * fixing flake check for darwin + passing check until pg_net * fix: start-server macos * fix: src yq from apt * fix: all instances yq * fix: jq invoke * fix: nix profile install * fix: nix run * fix: sudo nix run * chore: cut staging release * chore: rm debugging * fix: still need CURRENT_SYSTEM * test: re-introduce flake checks for 15 and 16 * chore: bump versions of patched pg for oriole to 17_4 and ext to beta6 * chore: bump var to release ami * chore: break down into functions * fix: skip x86 darwin for oriole nix builds * chore: cleanup repo for final review, rebase and merge * fix: try to properly init db in non oriole context * chore: restore installation of debug and src for all versions` * chore: newline * chore: cleaning up and restoring missing code * chore: cleanup * chore: fix gh action conditonal for oriole * fix: filter orioledb-17 from test on read --------- Co-authored-by: Oliver Rice <github@oliverrice.com>
1 parent d8e6a17 commit 54d80f6

27 files changed

+525
-202
lines changed

‎.github/workflows/test.yml

Lines changed: 3 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,10 @@
11
name: Test Database
2-
32
on:
43
push:
54
branches:
65
- develop
76
pull_request:
87
workflow_dispatch:
9-
108
jobs:
119
prepare:
1210
runs-on: ubuntu-latest
@@ -21,13 +19,11 @@ jobs:
2119
extra-conf: |
2220
substituters = https://cache.nixos.org https://nix-postgres-artifacts.s3.amazonaws.com
2321
trusted-public-keys = nix-postgres-artifacts:dGZlQOvKcNEjvT7QEAJbcV6b6uk7VF/hWMjhYleiaLI=% cache.nixos.org-1:6NCHdD59X431o0gWypbMrAURkbJ16ZPMQFGspcDShjY=
24-
2522
- name: Set PostgreSQL versions
2623
id: set-versions
2724
run: |
28-
VERSIONS=$(nix run nixpkgs#yq -- '.postgres_major[]' ansible/vars.yml | nix run nixpkgs#jq -- -R -s -c 'split("\n")[:-1]')
25+
VERSIONS=$(nix run nixpkgs#yq -- '.postgres_major[] | select(. != "orioledb-17")' ansible/vars.yml | nix run nixpkgs#jq -- -R -s -c 'split("\n")[:-1]')
2926
echo "postgres_versions=$VERSIONS" >> $GITHUB_OUTPUT
30-
3127
build:
3228
needs: prepare
3329
strategy:
@@ -45,49 +41,38 @@ jobs:
4541
POSTGRES_PASSWORD: password
4642
steps:
4743
- uses: actions/checkout@v4
48-
4944
- uses: DeterminateSystems/nix-installer-action@main
5045
with:
5146
extra-conf: |
5247
substituters = https://cache.nixos.org https://nix-postgres-artifacts.s3.amazonaws.com
5348
trusted-public-keys = nix-postgres-artifacts:dGZlQOvKcNEjvT7QEAJbcV6b6uk7VF/hWMjhYleiaLI=% cache.nixos.org-1:6NCHdD59X431o0gWypbMrAURkbJ16ZPMQFGspcDShjY=
54-
55-
5649
- name: Set PostgreSQL version environment variable
5750
run: echo "POSTGRES_MAJOR_VERSION=${{ matrix.postgres_version }}" >> $GITHUB_ENV
58-
5951
- name: Strip quotes from pg major and set env var
6052
run: |
6153
stripped_version=$(echo ${{ matrix.postgres_version }} | sed 's/^"\(.*\)"$/\1/')
6254
echo "PGMAJOR=$stripped_version" >> $GITHUB_ENV
63-
6455
- name: Generate common-nix.vars.pkr.hcl
6556
run: |
6657
PG_VERSION=$(nix run nixpkgs#yq -- '.postgres_release["postgres'${{ matrix.postgres_version }}'"]' ansible/vars.yml)
6758
PG_VERSION=$(echo $PG_VERSION | tr -d '"') # Remove any surrounding quotes
6859
echo 'postgres-version = "'$PG_VERSION'"' > common-nix.vars.pkr.hcl
69-
# Ensure there's a newline at the end of the file
7060
echo "" >> common-nix.vars.pkr.hcl
71-
7261
- id: settings
73-
# Remove spaces and quotes to get the raw version string
7462
run: sed -r 's/(\s|\")+//g' common-nix.vars.pkr.hcl >> $GITHUB_OUTPUT
75-
7663
- name: Generate args
7764
id: args
7865
run: |
7966
ARGS=$(nix run nixpkgs#yq -- 'to_entries | map(select(.value|type == "!!str")) | map(.key + "=" + .value) | join("\n")' ansible/vars.yml)
8067
echo "result<<EOF" >> $GITHUB_OUTPUT
8168
echo "$ARGS" >> $GITHUB_OUTPUT
8269
echo "EOF" >> $GITHUB_OUTPUT
83-
84-
#TODO PR Convert to develop branch flakeurl
8570
- name: verify schema.sql is committed
8671
run: |
8772
GIT_SHA=${{github.sha}}
88-
nix run github:supabase/postgres/${GIT_SHA}#dbmate-tool -- --version ${{ env.PGMAJOR }}
73+
nix run github:supabase/postgres/develop#dbmate-tool -- --version ${{ env.PGMAJOR }}
8974
if ! git diff --exit-code --quiet migrations/schema-${{ env.PGMAJOR }}.sql; then
9075
echo "Detected changes in schema.sql:"
9176
git diff migrations/schema-${{ env.PGMAJOR }}.sql
9277
exit 1
93-
fi
78+
fi

‎.github/workflows/testinfra-nix.yml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@ jobs:
1818
- name: Set PostgreSQL versions
1919
id: set-versions
2020
run: |
21-
VERSIONS=$(nix run nixpkgs#yq -- '.postgres_major[]' ansible/vars.yml | nix run nixpkgs#jq -- -R -s -c 'split("\n")[:-1]')
21+
VERSIONS=$(nix run nixpkgs#yq -- '.postgres_major[]' ansible/vars.yml | nix run nixpkgs#jq -- -R -s -c 'split("\n")[:-1]')
2222
echo "postgres_versions=$VERSIONS" >> $GITHUB_OUTPUT
2323
2424
test-ami-nix:
@@ -61,6 +61,7 @@ jobs:
6161

6262
- name: Set PostgreSQL version environment variable
6363
run: echo "POSTGRES_MAJOR_VERSION=${{ matrix.postgres_version }}" >> $GITHUB_ENV
64+
6465

6566
- name: Generate common-nix.vars.pkr.hcl
6667
run: |

‎.gitignore

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,4 +23,4 @@ result*
2323
.idea/
2424
.vscode/
2525

26-
db
26+
db

‎ansible/files/postgres_prestart.sh.j2

Lines changed: 42 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,49 @@
11
#!/bin/bash
22

3+
check_orioledb_enabled() {
4+
local pg_conf="/etc/postgresql/postgresql.conf"
5+
if [ ! -f "$pg_conf" ]; then
6+
return 0
7+
fi
8+
grep "^shared_preload_libraries" "$pg_conf" | grep -c "orioledb" || return 0
9+
}
10+
11+
get_shared_buffers() {
12+
local opt_conf="/etc/postgresql-custom/generated-optimizations.conf"
13+
if [ ! -f "$opt_conf" ]; then
14+
return 0
15+
fi
16+
grep "^shared_buffers = " "$opt_conf" | cut -d "=" -f2 | tr -d ' ' || return 0
17+
}
18+
19+
update_orioledb_buffers() {
20+
local pg_conf="/etc/postgresql/postgresql.conf"
21+
local value="$1"
22+
if grep -q "^orioledb.main_buffers = " "$pg_conf"; then
2 E377 3+
sed -i "s/^orioledb.main_buffers = .*/orioledb.main_buffers = $value/" "$pg_conf"
24+
else
25+
echo "orioledb.main_buffers = $value" >> "$pg_conf"
26+
fi
27+
}
28+
29+
main() {
30+
local has_orioledb=$(check_orioledb_enabled)
31+
if [ "$has_orioledb" -lt 1 ]; then
32+
return 0
33+
fi
34+
local shared_buffers_value=$(get_shared_buffers)
35+
if [ ! -z "$shared_buffers_value" ]; then
36+
update_orioledb_buffers "$shared_buffers_value"
37+
fi
38+
}
39+
40+
# Initial locale setup
341
if [ $(cat /etc/locale.gen | grep -c en_US.UTF-8) -eq 0 ]; then
4-
echo "en_US.UTF-8 UTF-8" >> /etc/locale.gen
42+
echo "en_US.UTF-8 UTF-8" >> /etc/locale.gen
543
fi
644

745
if [ $(locale -a | grep -c en_US.utf8) -eq 0 ]; then
8-
locale-gen
46+
locale-gen
947
fi
48+
49+
main

‎ansible/tasks/setup-postgres.yml

Lines changed: 43 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -204,23 +204,49 @@
204204
ansible_command_timeout: 60
205205
when: debpkg_mode
206206

207-
- name: Initialize the database stage2_nix
208-
become: yes
209-
become_user: postgres
210-
shell: source /var/lib/postgresql/.bashrc && /usr/lib/postgresql/bin/pg_ctl -D /var/lib/postgresql/data initdb -o "--allow-group-access" -o "--username=supabase_admin"
211-
args:
212-
executable: /bin/bash
213-
environment:
214-
LANG: en_US.UTF-8
215-
LANGUAGE: en_US.UTF-8
216-
LC_ALL: en_US.UTF-8
217-
LC_CTYPE: en_US.UTF-8
218-
LOCALE_ARCHIVE: /usr/lib/locale/locale-archive
219-
vars:
220-
ansible_command_timeout: 60
221-
# Circumvents the following error:
222-
# "Timeout (12s) waiting for privilege escalation prompt"
223-
when: stage2_nix
207+
- name: Check psql_version and modify supautils.conf and postgresql.conf if necessary
208+
block:
209+
- name: Check if psql_version is psql_orioledb
210+
set_fact:
211+
is_psql_oriole: "{{ psql_version in ['psql_orioledb-16', 'psql_orioledb-17'] }}"
212+
213+
- name: Initialize the database stage2_nix (non-orioledb)
214+
become: yes
215+
become_user: postgres
216+
shell: source /var/lib/postgresql/.bashrc && /usr/lib/postgresql/bin/pg_ctl -D /var/lib/postgresql/data initdb -o "--allow-group-access" -o "--username=supabase_admin"
217+
args:
218+
executable: /bin/bash
219+
environment:
220+
LANG: en_US.UTF-8
221+
LANGUAGE: en_US.UTF-8
222+
LC_ALL: en_US.UTF-8
223+
LC_CTYPE: en_US.UTF-8
224+
LOCALE_ARCHIVE: /usr/lib/locale/locale-archive
225+
vars:
226+
ansible_command_timeout: 60
227+
when: stage2_nix and not is_psql_oriole
228+
229+
- name: Initialize the database stage2_nix (orioledb)
230+
become: yes
231+
become_user: postgres
232+
shell: >
233+
source /var/lib/postgresql/.bashrc && initdb -D /var/lib/postgresql/data
234+
--allow-group-access
235+
--username=supabase_admin
236+
--locale-provider=icu
237+
--encoding=UTF-8
238+
--icu-locale=en_US.UTF-8
239+
args:
240+
executable: /bin/bash
241+
environment:
242+
LANG: en_US.UTF-8
243+
LANGUAGE: en_US.UTF-8
244+
LC_ALL: en_US.UTF-8
245+
LC_CTYPE: en_US.UTF-8
246+
LOCALE_ARCHIVE: /usr/lib/locale/locale-archive
247+
vars:
248+
ansible_command_timeout: 60
249+
when: stage2_nix and is_psql_oriole
224250

225251
- name: copy PG systemd unit
226252
template:

‎ansible/tasks/stage2-setup-postgres.yml

Lines changed: 68 additions & 9 deletions
D4AA
Original file line numberDiff line numberDiff line change
@@ -4,11 +4,56 @@
44
# sudo -u postgres bash -c ". /nix/var/nix/profiles/default/etc/profile.d/nix-daemon.sh && nix profile install nixpkgs#openjdk11"
55
# It was decided to leave pljava disabled at https://github.com/supabase/postgres/pull/690 therefore removing this task
66

7+
- name: Check psql_version and modify supautils.conf and postgresql.conf if necessary
8+
block:
9+
- name: Check if psql_version is psql_orioledb-16
10+
set_fact:
11+
is_psql_oriole: "{{ psql_version in ['psql_orioledb-16', 'psql_orioledb-17'] }}"
12+
13+
- name: Remove specified extensions from postgresql.conf if oriole-16 build
14+
ansible.builtin.command:
15+
cmd: >
16+
sed -i 's/ timescaledb,//g'
17+
/etc/postgresql/postgresql.conf
18+
when: is_psql_oriole and stage2_nix
19+
become: yes
20+
21+
- name: Remove specified extensions from supautils.conf if oriole-16 build
22+
ansible.builtin.command:
23+
cmd: >
24+
sed -i 's/ timescaledb,//g; s/ vector,//g; s/ plv8,//g; s/ postgis,//g; s/ pgrouting,//g'
25+
/etc/postgresql-custom/supautils.conf
26+
when: is_psql_oriole and stage2_nix
27+
become: yes
28+
29+
- name: Remove db_user_namespace from postgresql.conf if oriole-xx build
30+
ansible.builtin.command:
31+
cmd: >
32+
sed -i 's/db_user_namespace = off/#db_user_namespace = off/g;'
33+
/etc/postgresql/postgresql.conf
34+
when: is_psql_oriole and stage2_nix
35+
become: yes
36+
37+
- name: Append orioledb to shared_preload_libraries append within closing quote
38+
ansible.builtin.command:
39+
cmd: >
40+
sed -i 's/\(shared_preload_libraries.*\)'\''\(.*\)$/\1, orioledb'\''\2/'
41+
/etc/postgresql/postgresql.conf
42+
when: is_psql_oriole and stage2_nix
43+
become: yes
44+
45+
- name: Add default_table_access_method setting
46+
ansible.builtin.lineinfile:
47+
path: /etc/postgresql/postgresql.conf
48+
line: "default_table_access_method = 'orioledb'"
49+
state: present
50+
when: is_psql_oriole and stage2_nix
51+
become: yes
52+
753
- name: Install Postgres from nix binary cache
854
become: yes
955
shell: |
1056
sudo -u postgres bash -c ". /nix/var/nix/profiles/default/etc/profile.d/nix-daemon.sh && nix profile install github:supabase/postgres/{{ git_commit_sha }}#{{psql_version}}/bin"
11-
#TODO (samrose) switch pg_prove sourcing to develop branch once PR is merged
1257
when: stage2_nix
1358

1459
- name: Install pg_prove from nix binary cache
@@ -199,13 +244,19 @@
199244
recurse: yes
200245
when: stage2_nix
201246

202-
- name: Recursively create symbolic links and set permissions for the contrib/postgis-* dir
203-
shell: >
204-
sudo mkdir -p /usr/lib/postgresql/share/postgresql/contrib && \
205-
sudo find /var/lib/postgresql/.nix-profile/share/postgresql/contrib/ -mindepth 1 -type d -exec sh -c 'for dir do sudo ln -s "$dir" "/usr/lib/postgresql/share/postgresql/contrib/$(basename "$dir")"; done' sh {} + \
206-
&& chown -R postgres:postgres "/usr/lib/postgresql/share/postgresql/contrib/"
207-
become: yes
208-
when: stage2_nix
247+
- name: Check psql_version and run postgis linking if not oriole-xx
248+
block:
249+
- name: Check if psql_version is psql_orioledb-17
250+
set_fact:
251+
is_psql_oriole: "{{ psql_version == 'psql_orioledb-17' }}"
252+
253+
- name: Recursively create symbolic links and set permissions for the contrib/postgis-* dir
254+
shell: >
255+
sudo mkdir -p /usr/lib/postgresql/share/postgresql/contrib && \
256+
sudo find /var/lib/postgresql/.nix-profile/share/postgresql/contrib/ -mindepth 1 -type d -exec sh -c 'for dir do sudo ln -s "$dir" "/usr/lib/postgresql/share/postgresql/contrib/$(basename "$dir")"; done' sh {} + \
257+
&& chown -R postgres:postgres "/usr/lib/postgresql/share/postgresql/contrib/"
258+
become: yes
259+
when: stage2_nix and not is_psql_oriole
209260

210261
- name: Create symbolic links from /var/lib/postgresql/.nix-profile/share/postgresql/timezonesets to /usr/lib/postgresql/share/postgresql/timeszonesets
211262
file:
@@ -240,8 +291,16 @@
240291
line: pgsodium.getkey_script= '{{ pg_bindir }}/pgsodium_getkey.sh'
241292
when: stage2_nix
242293

294+
- name: Create symbolic link for pgsodium_getkey script
295+
file:
296+
src: "/usr/lib/postgresql/bin/pgsodium_getkey.sh"
297+
dest: "/usr/lib/postgresql/share/postgresql/extension/pgsodium_getkey"
298+
state: link
299+
become: yes
300+
when: stage2_nix
301+
243302
- name: Append GRN_PLUGINS_DIR to /etc/environment.d/postgresql.env
244303
ansible.builtin.lineinfile:
245304
path: /etc/environment.d/postgresql.env
246305
line: 'GRN_PLUGINS_DIR=/var/lib/postgresql/.nix-profile/lib/groonga/plugins'
247-
become: yes
306+
become: yes

0 commit comments

Comments
 (0)
0