Cloudron makes it easy to run web apps like WordPress, Nextcloud, GitLab on your server. Find out more or install now.


Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Bookmarks
  • Search
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Brand Logo

Cloudron Forum

Apps | Demo | Docs | Install
  1. Cloudron Forum
  2. PeerTube
  3. Restoring a psql dump from another instance

Restoring a psql dump from another instance

Scheduled Pinned Locked Moved PeerTube
2 Posts 1 Posters 44 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • D Offline
    D Offline
    DidierMalenfant
    wrote last edited by
    #1

    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.

    1 Reply Last reply
    2
    • D Offline
      D Offline
      DidierMalenfant
      wrote last edited by
      #2

      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.

      1 Reply Last reply
      4
      Reply
      • Reply as topic
      Log in to reply
      • Oldest to Newest
      • Newest to Oldest
      • Most Votes


      • Login

      • Don't have an account? Register

      • Login or register to search.
      • First post
        Last post
      0
      • Categories
      • Recent
      • Tags
      • Popular
      • Bookmarks
      • Search