New IHP Case Study: How AirPV is making the process of going solar easier and better value with IHP

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