Synology workarounds for port 5432 being used?

[apvlv]

Hello Elliot,

thank you very much for this project. On synology servers the psql port 5432 is internally in use and cannot be used for exposing on docker and Resolve itself (imho) is not able to connect to the db over custom defined ports. One workaround I found is use a macvlan network with docker to create a virtual interface but I'm feeling it is too much risk and overhead just for a port problem (see: https://www.schaupper.at/synology-davinci-resolve-shared-database/).

do you know any other way to accomplish the project server install on synology?

Thank you!

[syakeef]

Another workaround is to completely disable pgsql internally in Synology. Though some of Synology's store app depend on it, such as Video Station, Download Station, etc. If your NAS depend on it I wouldn't suggest do this step.

DSM 7

login via your ssh
sudo -i ; login as root account
su - postgres ; use postgres account
pg_ctl -D /var/services/pgsql stop ; this will stop running pgsql service
exit ; go back as root account
systemctl stop pgsql ; this stops pgsql
systemctl stop pgsql-adapter this stops running pgsql services
systemctl mask pgsql this will prevent pgsql from bootup
systemctl mask pgsql-adapter same as above

DSM 6

DSM 6 uses synoservice, so replace systemctl command with :
/usr/syno/sbin/synoservice –stop pgsql && etc

Then you can install this docker via docker-compose.
This works for me and I haven't had any system instability for weeks now

Making it work on Synology

Ps. before using docker-compose, I recommend change and add a few docker yaml lines :

services:
  postgres:
    image: postgres:13.0 ; using 13 defaults to the latest 13.7, which cause frequent database crash on my system
    shm_size: 4g ; this fix reindexdb out of memory issue when cleaning database using Resolve's optimize database (use 100m instead if your nas is limited in RAM)
    network_mode: "host" ; !!! BE SURE TO REMOVE 'ports : -  5432:5432' to avoid conflict !!!; without network host i couldn't get 2+ collaborators working together (Resolve will prompt network was blocked with firewall even though it wasn't)
    ...
  pgbackups:
    image: prodrigestivill/postgres-backup-local:13
    network_mode: "host" ; BE SURE TO REMOVE 'links : - postgres && depends on : - postgres' since we are using network host, this line isn't necessary and will cause conflict
  environment:
    - POSTGRES_HOST=localhost ; this will connect to our local nas ip for postgres backup
    ...
...

[elliotmatson]

@apvlv macvlan networks can be a bit finicky, I would probably just spin up a Virtual Machine on the NAS and then deploy the containers in there. @syakeef's solution would work but could break some things as he mentioned.

@syakeef I haven't seen any of the issues with the current docker-compose file that you mentioned, maybe they are tied to Synology? How much ram does your model have? I'll link to this discussion in the readme

[syakeef]

I'm running DS1621+ with 16GB of RAM.

Yeah running postgresql in Synology is a bit finnicky currently. I would've hoped they would fix it by DSM 7 but I guess we might have to wait for another major update

There might be another way without masking pgsql services by modifying Synology's internal pgsql port with vim /etc/postgresql/postgresql.conf and add port 5433 or something else and re-running pgsql. But I didn't have time to test it yet.

[elliotmatson]

@syakeef that's unfortunate. Odd that Synology would do things that way. Hopefully they change things, if they do I'm happy to add some documentation on using it!

I'm going to go ahead and convert this to a discussion until theres an easier way to fix this

[MoralesSan]

Hi. Trying to solve this for myself, and I think I found a reasonable solution and wanted to share my findings with the community.
First of all, I'm not using Docker in any way, I wanted to avoid that as I've been using Synology-stored SQL databases in Resolve for a few years now and never needed Docker before. I know it's super handy and whatnot, but this is just so you can get a recently dated version going natively.
It all started having trouble upgrading SQL databases from Resolve 17 to 18, tried everything including posting on the BM Resolve forums to never have an answer and then I found a compatibility chart that stated that the minimum psql version supported in 18 is 13, whereas the native psql that comes with the Synology can be as low as 9.3, depending on the DSM you are running.
Luckily I found this guide that goes in depth to explain what's going on. (In French) Turns out the author has compiled ready-to-install psql packages for the latest version, so you can get 13.2 this way.
You just need to add the repository "https://digitalbox.go.zd.fr/" to your Package Sources in DSM Package Center. Once you add this source you should see the packages in the Community section. I chose to install but not run PostgreSQL immediately after install, as we need to tweak some parameters.
It is very important to understand that this installs postgresql 13.2 in a different location than the default postgresql that comes with DSM. Since Resolve 18 expects a minimum version of 13, the standard 11.11 that comes with DSM 7 is not ideal. This can be confusing, so just to be clear the paths where these two separate postgresql installs live are as follows:
/etc/postgresql is the default postgresql (11.11) that comes with DSM, by default this service uses port 5432, and this is the only port Resolve will connect to.
/var/packages/postgresql/target/ (13.2) is the new postgresql we got installing from Package Manager using our custom source, by default this installation listens for connections on port 5433, which is not the port Resolve will talk to.
So what we need to do is edit the conf files for both services, restart them, and Resolve should be able to connect to the 13.2 psql.
To get this done, we can SSH into the Synology and then:

1. vi /etc/postgresql/postgresql.conf, by default this file does not include a "port" value, but we can just add "port = 5433" above the "listen_addresses" line. You may need to edit the listen_addresses as well, just use both local and the actual IP for your Synology.
2. You may also need to vi /etc/postgresql/pg_hba.conf and include the addresses you want to accept connections from.
3. We then vi /var/packages/postgresql/target/share/data/postgresql.conf and change the port value from 5433 to 5432.
4. Note you also need to vi /var/packages/postgresql/target/share/data/pg_hba.conf and include your listen addresses here.
5. We can then restart both postgresql services by doing pg_ctl -m fast restart for the default psql. (Need to be logged in as postgres user in your SSH connection)
6. For the 13.2 psql you can just click "Run" from the Package Manager.
7. And now you should be able to connect to PostgreSQL 13.2 from Resolve 18 without any issues.
   Surely there's a fancier way of doing it but to put actual projects in my 13.2 database I just exported them to a temp place and imported them onto the 13.2 psql database.
   This will persist even after a system reboot.

[elliotmatson]

Thanks @AntimodularResearch ! I'll link this in the README

[MoralesSan]

@elliotmatson That's great! Hope this will help someone 🤓

[emarchiol]

Sorry but how is this solution any different from starting a docker container on 5432 with host network and then change the port like you said on postgresql.conf to 5433 for the internal service?

I did exactly this and the internal pgsql service will start, the problem is that the pgsql-adapter service won't because it will only try to connect to 5432 port.

In my case I have a conflict where I need pgsql-adapter service for Synology Drive Server but at the same time I need a newer PostgreSQL version for DaVinci.

[syakeef]

You are correct in that some of Synology's services use internal pgsql. If you are having that issue the next best thing is to use MacVLAN. The benefit of doing via MacVLAN is it will not likely wipe your postgres config when there's a DSM update.

Here's an example of docker-compose.yml that I managed to get it running

version: '3.6'

services:
  postgres:
    container_name: resolve_pgsql
    image: postgres:13.4
    shm_size: 4g
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: DaVinci
    volumes:
      - "/volume1/System/PostgreSQL:/var/lib/postgresql/data"
    networks:
      intranet:
        ipv4_address: 192.168.0.68                 <----###### change this to available IP#1
  pgbackups:
    image: prodrigestivill/postgres-backup-local:13
    restart: always
    volumes:
      - "/volume1/System/ResolveBackup:/backups" 
    environment:
      - POSTGRES_HOST=192.168.0.68                 <----###### change this to IP#1 as above
      - POSTGRES_DB=shutterdatabase                <----###### !! this will be the database you are going to backup !!
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=DaVinci
      - POSTGRES_EXTRA_OPTS=--blobs --format=custom --quote-all-identifiers
      - BACKUP_SUFFIX=.backup
      - SCHEDULE=@daily
      - BACKUP_KEEP_DAYS=7
      - BACKUP_KEEP_WEEKS=4
      - BACKUP_KEEP_MONTHS=6
      - HEALTHCHECK_PORT=8080
      - TZ=Asia/Kuala_Lumpur                      <----###### change this to your TimeZone
    networks:
      intranet:
        ipv4_address: 192.168.0.69                <----###### change this to available IP#2

networks:
  intranet:
    name: intranet
    driver: macvlan
    driver_opts:
      parent: ovs_bond0                           <----###### change this to your network driver
    ipam:
      config:
        - subnet: "192.168.0.0/20"                <----###### change this to your network subnet
          gateway: "192.168.0.1"                  <----###### change this to your network gateway

Keep in mind this isn't the latest docker compose file from this repository, as it only has pgsql, pgbackup and healthcheck. I don't use pgadmin but if you want them you can add in the compose file.

You Have To Change ipv4 Addresses, Network Driver Parent, and ipam Config and if necessary Your Volume Location. You can find your parent driver interface and read more about macvlan here.

I also changed postgres:13.4 (since this is the version Blackmagic use for Davinci Resolve Server Installation) and shm_size: 4g (to prevent reindexdb and vacuuming error when doing database maintenance).

P.S, I know that I could use the same ip address for pgsql and pgbackups healthcheck (since only the ports are different) but I prefer using 2 different IPs for compatibility... I think

Edit, create/connect database in Davinci Resolve using your IP#1

[qq527364584]

Hello, I use macvlan and set the virtual ip address and port number according to it, merge the latest docker-compose file, and also need pgadmin to manage and back up the da Vinci database file, but input ip and address port number in pgadmin, and I can't connect to this postgre database all the time, tossing for a long time, hoping to get a reply solution. Or can't you merge a docker-compose file about Qunhui for me to test @elliotmatson @syakeef Thank you

#---

version: '3.8'
x-common:
database: &db-environment
POSTGRES_DB: database
POSTGRES_USER: &pg-user postgres
POSTGRES_PASSWORD: DaVinci
TZ: TZ=Asia/Shanghai
POSTGRES_LOCATION: &db-location "/volume1/docker/DaVinci/db:/var/lib/postgresql/data"
backup: &backup-environment
SCHEDULE: "@every 5 minutes"
BACKUP_KEEP_DAYS: 7
BACKUP_KEEP_WEEKS: 4
BACKUP_KEEP_MONTHS: 6
BACKUP_LOCATION: &bk-location "/volume1/docker/DaVinci/ResolveBackup:/backups"
admin: &admin-environment
PGADMIN_DEFAULT_EMAIL: [email protected]
PGADMIN_DEFAULT_PASSWORD: root
PGADMIN_PORT: &pgadmin-port "3001:80"

below if you understand what you are doing.

services:
postgres:
container_name: resolve_pgsql
image: postgres:13
shm_size: 4g
restart: always
ports:
- "5432:5432"
environment:
<<: [*db-environment]
volumes:
- *db-location
networks:
intranet:
ipv4_address: 192.168.99.9
healthcheck:
test: ["CMD", "pg_isready", "-U", *pg-user]
interval: 10s
timeout: 5s
retries: 5
pgbackups:
container_name: resolve_pgbackup
image: prodrigestivill/postgres-backup-local:13
restart: always
volumes:
- *bk-location
environment:
<<: [*db-environment, *backup-environment]
POSTGRES_HOST: 192.168.99.9
POSTGRES_EXTRA_OPTS: --blobs --format=custom --quote-all-identifiers
BACKUP_SUFFIX: .backup
HEALTHCHECK_PORT: 8080
healthcheck:
interval: 30s
networks:
intranet:
ipv4_address: 192.168.99.7
pgadmin:
container_name: resolve_pgadmin
image: dpage/pgadmin4
restart: always
environment:
<<: [*admin-environment]
ports:
- *pgadmin-port
volumes:
- pgadmin-config:/pgadmin4-config
- pgadmin:/var/lib/pgadmin
- *bk-location
entrypoint: "/bin/sh"
command:
- -c
- |
mkdir -p /var/lib/pgadmin/storage/$${PGADMIN_DEFAULT_EMAIL//@/}/
ln -s /backups /var/lib/pgadmin/storage/$${PGADMIN_DEFAULT_EMAIL//@/}/
/entrypoint.sh
depends_on:
- postgres
- pgadmin-config
healthcheck:
test: ["CMD-SHELL", "wget --no-verbose --tries=1 --spider http://localhost/ || exit 1"]
pgadmin-config:
container_name: resolve_pgadmin-config-creator
image: ghcr.io/elliotmatson/pgadmin-config-creator:latest
restart: on-failure
environment:
<<: [*db-environment]
PGADMIN_SERVER_JSON_FILE: /pgadmin4-config/servers.json
volumes:
- pgadmin-config:/config
volumes:
pgadmin-config:
pgadmin:
networks:
intranet:
name: macv
driver: macvlan
driver_opts:
parent: ovs_eth4
ipam:
config:
- subnet: "192.168.99.0/24"
gateway: "192.168.99.1"

[elliotmatson]

Unfortunately I don't have access to a synology NAS, so I don't know how helpful I'll be.

Can you connect to the Postgres DB from another device? Or is nothing able to access it?

It's been a while since I've used the macvlan driver in docker, but I remember having a lot of problems with containers on the same host communicating with each other

[syakeef]

Hi there, if you did the McVLAN route maybe you could check your router settings (check for DHCP, AP table, connected device lists. etc.) or check your synology firewall. Try ping from your computer to your postgres ipv4 address and see if it reaches.

[MoralesSan]

Just dropping by to report that my PostgreSQL method described above works flawlessly for Resolve 18.6

[ramonskie]

note:

replace 192.168.1

solution

the easiest solution i have is:

set the router dhcp ip pool end range to 192.168.1.200

using the following docker-compose.yml

---
version: '3.8'
x-common:
  database: &db-environment
    POSTGRES_DB: database
    POSTGRES_USER: &pg-user postgres
    POSTGRES_PASSWORD: DaVinci
    TZ: Europe/Amsterdam
    POSTGRES_LOCATION: &db-location "/volume3/docker/davinci/database:/var/lib/postgresql/data"
  backup: &backup-environment
    SCHEDULE: "@daily"
    BACKUP_KEEP_DAYS: 7
    BACKUP_KEEP_WEEKS: 4
    BACKUP_KEEP_MONTHS: 6
    BACKUP_LOCATION: &bk-location "/volume1/data/davinci/backup:/backups"
  admin: &admin-environment
    PGADMIN_DEFAULT_EMAIL: [email protected]
    PGADMIN_DEFAULT_PASSWORD: root
    PGADMIN_PORT: &pgadmin-port "3001:80"
#
# ------------------------------------------------------------------------------------------
# DANGER ZONE BELOW
#
# The remainder of this file likely does not need to be changed. 
# Please only make modifications
# below if you understand what you are doing.
#
services:
  postgres:
    container_name: resolve_pgsql
    image: postgres:13
    restart: always
    ports:
      - "5432:5432"
    environment:
      <<: [*db-environment]
    volumes:
      - *db-location
    healthcheck:
      test: ["CMD", "pg_isready", "-U", *pg-user]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      macvlan:
        ipv4_address: 192.168.1.201
  pgbackups:
    container_name: resolve_pgbackup
    image: prodrigestivill/postgres-backup-local:13
    restart: always
    volumes:
      - *bk-location
    depends_on:
      - postgres
    environment:
      <<: [*db-environment, *backup-environment]
      POSTGRES_HOST: 192.168.1.201
      POSTGRES_EXTRA_OPTS: --blobs --format=custom --quote-all-identifiers
      BACKUP_SUFFIX: .backup
      HEALTHCHECK_PORT: 8080
    healthcheck:
      interval: 30s
  pgadmin:
    container_name: resolve_pgadmin
    image: dpage/pgadmin4
    restart: always
    environment:
      <<: [*admin-environment]
    ports:
      - *pgadmin-port
    volumes:
      - pgadmin-config:/pgadmin4-config
      - pgadmin:/var/lib/pgadmin
      - *bk-location
    entrypoint: "/bin/sh"
    command: 
      - -c
      - |
        mkdir -p /var/lib/pgadmin/storage/$${PGADMIN_DEFAULT_EMAIL//@/_}/
        ln -s /backups /var/lib/pgadmin/storage/$${PGADMIN_DEFAULT_EMAIL//@/_}/
        /entrypoint.sh
    depends_on:
      - postgres
      - pgadmin-config
    healthcheck:
      test: ["CMD-SHELL", "wget --no-verbose --tries=1 --spider http://localhost/ || exit 1"]
  pgadmin-config:
    container_name: resolve_pgadmin-config-creator
    image: ghcr.io/elliotmatson/pgadmin-config-creator:latest
    restart: on-failure
    environment:
      <<: [*db-environment]
      PGADMIN_SERVER_JSON_FILE: /pgadmin4-config/servers.json
    volumes:
      - pgadmin-config:/config

networks:
  macvlan:
    name: macvlan
    driver: macvlan
    driver_opts:
      parent: eth0
    ipam:
      config:
        - subnet: "192.168.1.0/24"
          gateway: "192.168.1.1"                 

volumes:
  pgadmin-config:
  pgadmin:

[KonRay]

Hi everyone,

Thank you all for sharing your configs, they helped me a lot.
I managed to setup Davinci Resolve network database, pgadmin and automatic backups.
While everything seems to be working fine, I struggle to restore db backups created this way.
Despite having the same *.backup extension as manual Davinci Reslove backups created via Project Manager. I can't restore them the same way.
What is the right way to restore those backups?

[syakeef]

Hi,

I just use Resolve's restore backup database, point to *.backup, but use different database name (like Database-2). Usually it'll automatically create a new database and connect to it. You might have to connect to that database if it doesn't.
You can check pgadmin if any new database has been created.

[KonRay]

Thanks for confirming it should work via the Project Manager.
I have encountered a glitch that has been confusing me. After initiating a backup restore, a blank cmd window pops up and it fails to show any progress. If I close it within a minute or two, the restored database becomes corrupted. However, if I leave it for about 10 minutes and then close it, the database will be restored successfully. Quite peculiar behaviour. Hopefully, it will work fine in the much anticipated DaVinci Resolve 19 😉