IHP 1.3 is out now!

Pulling production data base into local instsance

Joachim Breitner

I’d like to pull the data from the IHPCloud deployed instance into my local database. I tried

pg_dump "postgresql://…?host=database-cloud.cephcue7lnqe.eu-central-1.rds.amazonaws.com" | make psql

but the output isn’t promising:

SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
CREATE EXTENSION
COMMENT
ERROR:  type "job_status" already exists
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf" does not exist
ERROR:  function "ihp_user_id" already exists with same argument types
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf" does not exist
ERROR:  function "notify_did_change_registrations" already exists with same argument types
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf" does not exist
SET
ERROR:  relation "keyholders" already exists
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf" does not exist
ERROR:  relation "logs" already exists
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf" does not exist
ERROR:  relation "registrations" already exists
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf" does not exist
ERROR:  relation "schema_migrations" already exists
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf" does not exist
ERROR:  relation "users" already exists
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf" does not exist
ERROR:  duplicate key value violates unique constraint "keyholders_key_number_key"
DETAIL:  Key (key_number)=(2) already exists.
CONTEXT:  COPY keyholders, line 1
ERROR:  insert or update on table "logs" violates foreign key constraint "logs_ref_user_id"
DETAIL:  Key (user_id)=(aad91fd0-7d5c-4e06-9a81-2e52fa912eea) is not present in table "users".
ERROR:  insert or update on table "registrations" violates foreign key constraint "registrations_ref_player_user"
DETAIL:  Key (player_user)=(1f49bde0-df60-4bec-9835-301e3a2af4af) is not present in table "users".
ERROR:  duplicate key value violates unique constraint "schema_migrations_revision_key"
DETAIL:  Key (revision)=(1654362112) already exists.
CONTEXT:  COPY schema_migrations, line 1
ERROR:  duplicate key value violates unique constraint "users_buhl_id_key"
DETAIL:  Key (buhl_id)=(60a85b10-0cef-48e1-b8cd-752e4779bc55) already exists.
CONTEXT:  COPY users, line 5
ERROR:  relation "keyholders_key_number_key" already exists
ERROR:  multiple primary keys for table "keyholders" are not allowed
ERROR:  multiple primary keys for table "logs" are not allowed
ERROR:  multiple primary keys for table "registrations" are not allowed
ERROR:  relation "schema_migrations_revision_key" already exists
ERROR:  relation "users_buhl_id_key" already exists
ERROR:  multiple primary keys for table "users" are not allowed
ERROR:  relation "logs_created_at_index" already exists
ERROR:  relation "logs_user_id_index" already exists
ERROR:  relation "registrations_created_at_index" already exists
ERROR:  trigger "did_delete_registrations" for relation "registrations" already exists
ERROR:  trigger "did_insert_registrations" for relation "registrations" already exists
ERROR:  trigger "did_update_registrations" for relation "registrations" already exists
ERROR:  constraint "logs_ref_user_id" for relation "logs" already exists
ERROR:  constraint "registrations_ref_player_user" for relation "registrations" already exists
ERROR:  role "rdsadmin" does not exist
REVOKE
ERROR:  role "postgres" does not exist
GRANT
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf_ihp_authenticated" does not exist
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf_ihp_authenticated" does not exist
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf_ihp_authenticated" does not exist
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf_ihp_authenticated" does not exist
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf_ihp_authenticated" does not exist
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf_ihp_authenticated" does not exist
ERROR:  role "UfUnLItbyXdwiXXebAJzhSqScoDVptNf_ihp_authenticated" does not exist

Is there a clean way to achieve this?

Lillo PRO

Hi!

It's been some time since I have used it, but here is a script that I have used for fetching the production database into the local dev database:

#!/usr/bin/env bash

pg_dump "postgresql://…?host=database-cloud.cephcue7lnqe.eu-central-1.rds.amazonaws.com" -a --inserts --column-inserts --disable-triggers | sed -e '/^--/d' >Application/Fixtures.sql

# Import Fixtures
make db

# Dump Fixtures
make dump_db