PostgreSQL 11.x including wizard install/uninstall

[hgy59]

Motivation: This is a followup of the PR #3826 (fork was deleted) Linked issues: #3826

Checklist

• [x] Build rule all-supported completed successfully
• [x] Package upgrade completed successfully
• [x] New installation of package completed successfully

TODO:

• [ ] create firewall rule (*.sc) for port defined in wizard
• [ ] add support for postgis

[hgy59]

@DigitalBox98 @ymartin59 are you still interested in PostgreSQL package? It is now working on DSM 7 and DSM 6.

[DigitalBox98]

Yes :)

[RMHogervorst]

I can't get this build for alpine. Am I missing someting?

I tried:

• make spk-postgres-alpine-7.0 from the rootfolder
• moving into the postgres folder and running make alpine-7.0
• idem with make syno-alpine-7.0 result: make: *** No rule to make target 'syno-alpine-7.0'. Stop.

[hgy59]

@RMHogervorst did you try (in the spk/postgres folder)?

make arch-alpine-7.0

an alternative would be:

make ARCH=alpine TCVERSION=7.0

[RMHogervorst]

wow, that was the piece of missing information! I think I missed this because the developer docs talk about make arch-88f6281 and I read that as make [architecturestring], but the arch part is separate!

It now fails on zlib

Downloading files for zlib
===>    wget https://zlib.net/zlib-1.2.11.tar.gz
https://zlib.net/zlib-1.2.11.tar.gz:
2023-01-09 06:26:32 ERROR 404: Not Found.

[RMHogervorst]

it seems the current version of zlib is 1.2.13 (released October 13, 2022 ) because of a cve all previous versions are unavailable I think.

Here are the changes, not in an PR, not just because I am lazy, but also because I think you should verify the hash digests yourself since I am a random person on the internet.

cross/zlib/makefile

PKG_NAME = zlib
PKG_VERS = 1.2.13
PKG_EXT = tar.gz
PKG_DIST_NAME = $(PKG_NAME)-$(PKG_VERS).$(PKG_EXT)
PKG_DIST_SITE = https://zlib.net
PKG_DIR = $(PKG_NAME)-$(PKG_VERS)

DEPENDS =

HOMEPAGE = https://zlib.net/
COMMENT  = A Massively Spiffy Yet Delicately Unobtrusive Compression Library.
LICENSE  = zlib-license

ADDITIONAL_CFLAGS = -Os

include ../../mk/spksrc.cross-cc.mk

and the digests in cross/zlib/digests

zlib-1.2.13.tar.gz SHA1 55eaa84906f31ac20d725aa26cd20839196b6ba6
zlib-1.2.13.tar.gz SHA256 b3a24de97a8fdbc835b9833169501030b8977031bcb54b3b3ac13740f846ab30
zlib-1.2.13.tar.gz MD5 9b8aa094c4e5765dabf4da391f00d15c

[hgy59]

@RMHogervorst we already have the current zlib on the master branch. So I have rebased this PR.

BTW: for alpine you can use the armv7 package created by the github build action (when it is successfully completed, and within 90 days).

[RMHogervorst]

I feel really stupid, where can I find the installation docs? I get invalid file format. contact developers I tried manual install within DSM with the zipfile for arm v7 and arm v7-6.1 but also the file inside.

[hgy59]

you must install *.spk files (not zip)

[RMHogervorst]

alright this is what I tried version 1

• change extension of zip file to spk
• install the spk file (did not work)

version 2

• unpack the downloaded zip file
• tar the file and turn it into a spk file tar -cvzf postgresql.spk postgresql_armv7-7.0_11.16-3
• install the spk file (did not work)

[hgy59]

1. download the artifacts (*.zip)
2. unpack the artifacts -> you will get the spk
3. In DSM Package center of your Diskstation chose "Manual Install" and select the spk file to upload and install

[RMHogervorst]

Alright this is what lives in the unpacked zipfile. Did something go wrong? because there is no spk file here.

postgresql_armv7-7.0_11.16-3 tree
.
├── INFO
├── PACKAGE_ICON.PNG
├── PACKAGE_ICON_256.PNG
├── WIZARD_UIFILES
│   ├── install_uifile
│   ├── uninstall_uifile
│   └── upgrade_uifile.sh
├── conf
│   ├── privilege
│   └── resource
├── package.tgz
└── scripts
    ├── functions
    ├── installer
    ├── postinst
    ├── postuninst
    ├── postupgrade
    ├── preinst
    ├── preuninst
    ├── preupgrade
    ├── service-setup
    └── start-stop-status

3 directories, 19 files

[hgy59]

I have not a clue what's going wrong.

1. navigate to "checks - build -artifacts"
2. click (e.g.) "Packages for aarch64-6.1"
3. it will download Packages for aarch64-6.1.zip
4. unpack the downloaded zip will extract postgresql_aarch64-6.1_11.16-3.spk

This should work for all archs (I have also validated the armv7-6.1 and armv7-7.0 artifacts)

The tree you showed above looks like the content of package.tgz within the spk file.

[RMHogervorst]

Wow this is really weird, it must be a mac specific (or my computer specific) issue. When I use the standard macos build in tool to open the zip, it opens to the folder as I described, but when I use a different tool it unzips to the spk file!

Thanks for the help!

[RMHogervorst]

ah shoot! it doesn't install because the package requires root privileges:

[hgy59]

@RMHogervorst can you please try with the new version? (didn't test it, but the installer should work)

[RMHogervorst]

Awesome! it installs cleanly! I don't have the time (for the next 10 hours ) to check if everything works, but I see no errors during installation and it seems to 'just' work.

[RMHogervorst]

There might be an issue in the long run with no superuser to do maintainence?

 tail postgresql.log
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [6-1] 2023-01-10 00:14:18.543 GMT [16794] WARNING:  skipping "pg_subscription" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [7-1] 2023-01-10 00:14:18.825 GMT [16794] WARNING:  skipping "pg_database" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [8-1] 2023-01-10 00:14:18.825 GMT [16794] WARNING:  skipping "pg_db_role_setting" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [9-1] 2023-01-10 00:14:18.825 GMT [16794] WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [10-1] 2023-01-10 00:14:18.841 GMT [16794] WARNING:  skipping "pg_pltemplate" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [11-1] 2023-01-10 00:14:18.841 GMT [16794] WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [12-1] 2023-01-10 00:14:18.842 GMT [16794] WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [13-1] 2023-01-10 00:14:18.842 GMT [16794] WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [14-1] 2023-01-10 00:14:18.848 GMT [16794] WARNING:  skipping "pg_replication_origin" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [15-1] 2023-01-10 00:14:18.850 GMT [16794] WARNING:  skipping "pg_shseclabel" --- only superuser can vacuum it
ash-4.4# tail postgresql.log
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [6-1] 2023-01-10 00:14:18.543 GMT [16794] WARNING:  skipping "pg_subscription" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [7-1] 2023-01-10 00:14:18.825 GMT [16794] WARNING:  skipping "pg_database" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [8-1] 2023-01-10 00:14:18.825 GMT [16794] WARNING:  skipping "pg_db_role_setting" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [9-1] 2023-01-10 00:14:18.825 GMT [16794] WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [10-1] 2023-01-10 00:14:18.841 GMT [16794] WARNING:  skipping "pg_pltemplate" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [11-1] 2023-01-10 00:14:18.841 GMT [16794] WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [12-1] 2023-01-10 00:14:18.842 GMT [16794] WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [13-1] 2023-01-10 00:14:18.842 GMT [16794] WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [14-1] 2023-01-10 00:14:18.848 GMT [16794] WARNING:  skipping "pg_replication_origin" --- only superuser can vacuum it
2023-01-10T01:14:18+01:00 netwerkhdd postgres[16794]: [15-1] 2023-01-10 00:14:18.850 GMT [16794] WARNING:  skipping "pg_shseclabel" --- only superuser can vacuum it

[hgy59]

@RMHogervorst do you know what cleanup script/task is run by postgresql? we should disable the pgsql internal cleanup and run a scheduled task in DSM (yes, those task can run scripts with root privileges in DSM 7).

[RMHogervorst]

Not from the top of my head, but I guess this https://www.postgresql.org/docs/14/maintenance.html I will check the pg_cron table and dig a bit further

21 jan. 2023 20:01:39 hgy59 @.***>:

@RMHogervorst[https://github.com/RMHogervorst] do you know what cleanup script/task is run by postgresql? we should disable the pgsql internal cleanup and run a scheduled task in DSM (yes, those task can run scripts with root privileges in DSM 7).

— Reply to this email directly, view it on GitHub[https://github.com/SynoCommunity/spksrc/pull/4267#issuecomment-1399312455], or unsubscribe[https://github.com/notifications/unsubscribe-auth/ACIYT6BG2ISYVVPGMXJ2V4TWTQXBFANCNFSM4TXSLHJA]. You are receiving this because you were mentioned.[Tracking afbeelding][https://github.com/notifications/beacon/ACIYT6BMZXWYDJCKNKUX27TWTQXBFA5CNFSM4TXSLHJKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOKNT5ARY.gif]

[RMHogervorst]

As far as I can see these are automatic cleanup tasks from the 'Autovacuum Daemon'.

• autovacuum VACUUM
• updating the statistics on tables ANALYZE

The errors only happen on the system (pg_catalog) tables. so pg_tablespace, db_role_settings, pg_database etc. I don't think this will cause many problems (but I'm no dba)

The settings for this deamon should be in the postgresql.conf file. But autovacuum = off in my /etc/postgresql/postgresql.conf file.

So, I don't have a clue what is going on here.

[osmeest]

Hi, Is there any reason why this PR is not getting merged into master ? Looking at the scripts in the branch, I don't see timescaledb extension being included as was originally requested in https://github.com/SynoCommunity/spksrc/issues/3815. Correct ?

[hgy59]

Hi, Is there any reason why this PR is not getting merged into master ?

There are still issues with cleanup that need to be solved.

Looking at the scripts in the branch, I don't see timescaledb extension being included as was originally requested in #3815. Correct ?

timescaledb is not related to this PR (it is a different product/project, not a pgsql extension).

[DigitalBox98]

It seems the solution consists to deactivate auto vacuum and to create a DSM task to replace it.

If I have some time I will look at it (some useful information are in https://hevodata.com/learn/postgresql-vacuum-command/)

[DigitalBox98]

@RMHogervorst

A few findings on my side :

• Postgresql integrated to DSM doesn't use the vacuum as the properfy is off (see below screenshot from /etc/postgresql/postgresql.conf, owner is "postgres")
• By default, I didn't see any usage for Postgresql 11 and 13 of the vacuum (in /var/packages/postgresql/var/data/postgresql.conf the line is commented #autovacuum = on)

How did you get the vacuum messages in your logs ?

I've tried to launch the vacuum and I didn't get any error : /var/packages/postgresql/target/bin/psql -U pgadmin -b postgres -p 5433 vacuum full;

I've actived the vacuum to on, and I will check in the coming days if I see any error on my side.

[RMHogervorst]

It has been a while since I looked into the logs, I don't remember anymore.

[DigitalBox98]

@hgy59 I've got version 13.2 compiling and running fine on my NAS, are you interested in this version ?

[RMHogervorst]

I'd love to check it out, but it will be a while before I have the time