Restoring a psql dump from another instance
-
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.
-
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
andpg_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.