[TECH] ver 3.35.x : postgreSQL dump and restore

intro

https://www.postgresql.org/docs/9.1/backup-dump.html

History-edit : latest =2019-12-02

database is seqdb_prod

db-dump from prod

manual

1. pg_dump -U 'dbname' > outfile
2. pg_dump -U postgres seqdb_prod > seqdb_prod-20191025.sql

script : 'postgres_backup.sh'

#!/bin/bash
source ../env/.env.postgres
NOW=`date +"%Y-%m-%d"`
TARGET=../db-backup
docker exec shared_db sh -c 'exec  pg_dump -U postgres seqdb_prod' > ${TARGET}/${POSTGRES_DB}_${NOW}.sql
echo $NOW >> biobackup.log
scp ${TARGET}/${POSTGRES_DB}_${NOW}.sql biobackup:/backup/SEQDB &>>biobackup.log

restore

Using new volume

proof-of-concept

(A) Pre-req

0. edit the docker-compose.yml-file; use a new volume
1. run 'make',
2. seqdb-lab.nrm.se - login as 'Admin'
3. Specimen and Samples should be empty

(B) restore Syntax : psql -U 'dbname' < infile

0. drop schema seqdb cascade;

1. psql -U postgres -d seqdb_prod < seqdb_prod_2019-11-20.sql

2. docker cp seqdb_prod_2019-11-20.sql shared_db:/tmp

3. docker exec -it shared_db bash

4. cd /tmp

5. psql -U postgres seqdb_prod < seqdb_prod_2019-11-20.sql or psql -h localhost -U postgres -d seqdb_prod -f seqdb_prod_2019-11-20.sql

6. docker-compose down , make

restore, verify

0. docker exec -it shared_db bash
1. psql -d seqdb_prod -U postgres
2. SET search_path TO seqdb;
3. select * from seqdb.accounts or (select * from accounts , works if search_path is correct)

start with org docker-compose.yml, then restart with edited docker-compose.yml

1. add user
2. add account
3. add group -> 'NRM-CGI' -> all 'rights'
4. add specimen collection ->