Couple of clarifications for the script, which aren't obvious:

Assaf Gordon wrote, On 03/25/2013 02:03 PM:

To facilitate that, we use the following script, that can quickly copy the data from our production server to our development server. Once it's run, the databases contain the same datasets and workflows and everything, making testing and debugging much easier:

=== #!/bin/sh

DATE=$(date "+%Y_%m_%d_%H%M%S")

FILE="galaxy_db_prod_to_devel_${DATE}.sql.gz"

DEST=/tmp/ FILE="${DEST}${FILE}"

echo "Dumping Prod-DB to:" echo " $FILE" echo "(Enter 'galaxyprod' DB password)" pg_dump -c -U galaxyprod galaxyprod | sed 's/galaxyprod/galaxydev/g' | gzip > "$FILE" || exit 1

echo echo "Dropping and re-creating Galaxy-Devel database." echo "press CTRL-C to abort or ENTER to continue." read ## First, drop all tables, functions, etc.

psql -t -d galaxydev -c "SELECT 'DROP TABLE ' || n.nspname || '.' ||c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid)" | psql galaxydev zcat "$FILE" | psql galaxydev || exit 1

## Reset all the waiting jobs, we don't want to re-run them on dev psql galaxydev -c "update job set state='error' where (state='new' or state='waiting');"

rm "$FILE"

Database "galaxyprod" with user "galaxyprod" are configured to ask for a password, which is why: pg_dump -c -U galaxyprod galaxyprod Will stop and ask for a password. Local unix users can't automatically access "galaxyprod".

Database "galaxydev" is configured to be accessible to few local unix users, which is why: psql galaxydev Doesn't need an explicit user name and won't ask for a password (and is run multiple times in the script).

This is configured in "/var/lib/pgsql/data/pg_hba.conf" as: === # TYPE DATABASE USER CIDR-ADDRESS METHOD local galaxydev gordon trust ===

Whether it's secure/optimal or not is open for discussion...

The convoluted SQL is used to drop all tables before adding the new ones. It's based on this message: http://www.postgresql.org/message-id/1093378065.15248.36.camel@linda

With this script we also test database upgrades without worries, as we first copy the prod database to dev, then run "sh manage_db.sh upgrade" on the development database. If it works - great. If not (rare, but happened once or twice), we do not upgrade prod.

-gordon