Restoring a psql dump from another instance

[DidierMalenfant]

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.

[DidierMalenfant]

I think I may have found the solution. I can drop everything in the old database by logging in to the psql database and doing:

DROP OWNED BY userXXXXXX CASCADE;

(obviously replacing the XXXX by the random number generated for the peertube app) and then recreating an empty schema ready form import:

CREATE SCHEMA public;
CREATE EXTENSION unaccent;
CREATE EXTENSION pg_trgm;

(unaccent and pg_trgm are needed for restoring the peer tube db dump).

Then the restore works without error or warnings:

PGPASSWORD=${CLOUDRON_POSTGRESQL_PASSWORD} pg_restore -h ${CLOUDRON_POSTGRESQL_HOST} -p ${CLOUDRON_POSTGRESQL_PORT} -U ${CLOUDRON_POSTGRESQL_USERNAME} --format=custom --schema=public --no-owner --role=${CLOUDRON_POSTGRESQL_USERNAME} -d ${CLOUDRON_POSTGRESQL_DATABASE} peertube_prod-dump.db

and my app seems to come back up correctly as my old peertube server.

I'll keep an eye on it in the next couple of days to see if I notice anything odd.