I'm moving my peer tube instance over to my Cloudron server and I'm trying to follow their instructions on how to to migrate it.
I have a dump of the database but trying to restore it on the Cloudron instance is difficult because I seem to lack privileges in order to delete and/or recreate the database.
This is what I'm currently trying:
PGPASSWORD=${CLOUDRON_POSTGRESQL_PASSWORD} pg_restore -h ${CLOUDRON_POSTGRESQL_HOST} -p ${CLOUDRON_POSTGRESQL_PORT} -U ${CLOUDRON_POSTGRESQL_USERNAME} --data-only --format=custom --schema=public --no-owner --role=${CLOUDRON_POS
TGRESQL_USERNAME} -d ${CLOUDRON_POSTGRESQL_DATABASE} peertube_prod-dump.db
which fails because of the existing data in the database:
...
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY account, line 1
pg_restore: error: COPY failed for table "actor": ERROR: duplicate key value violates unique constraint "actor_pkey"
...
Trying to delete the db and recreate it with:
PGPASSWORD=${CLOUDRON_POSTGRESQL_PASSWORD} pg_restore -h ${CLOUDRON_POSTGRESQL_HOST} -p ${CLOUDRON_POSTGRESQL_PORT} -U ${CLOUDRON_POSTGRESQL_USERNAME} --clean --create --format=custom --schema=public --no-owner --role=${CLOUDRON_POSTGRESQL_USERNAME} -d ${CLOUDRON_POSTGRESQL_DATABASE} peertube_prod-dump.db
also fails with:
pg_restore: error: could not execute query: ERROR: database "peertube_prod" does not exist
Command was: DROP DATABASE peertube_prod;
pg_restore: error: could not execute query: ERROR: permission denied to create database
Command was: CREATE DATABASE peertube_prod WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'C.UTF-8';
pg_restore: error: reconnection failed: connection to server at "postgresql" (xxxxx), port 5432 failed: FATAL: no pg_hba.conf entry for host "xxxxx", user "userXXXXXX", database "peertube_prod", no encryption
which seems to be because it's trying to recreate the original db and not the one that the Cloudron server expects.