Re: [galaxy-dev] Production and Dev server on the same database.
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 DATABASEUSERCIDR-ADDRESS METHOD
local galaxydev gordontrust
===
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
___
Please keep all replies on the list by using "reply all"
in your mail client. To manage your subscriptions to this
and other Galaxy lists, please use the interface at:
http://lists.bx.psu.edu/
To search Galaxy mailing lists use the unified search at:
http://galaxyproject.org/search/mailinglists/
Re: [galaxy-dev] Production and Dev server on the same database.
Hello Ted!
Peter Cock wrote, On 03/25/2013 01:51 PM:
> On Mon, Mar 25, 2013 at 5:41 PM, Ted Goldstein wrote:
>> Hi
>> Does anyone have any experience or advice about running
>> two servers on the same database?
>>
>
> My advice is don't do it.
I'll second Peter's advice, don't ever do that :)
I forgot where I heard it, but someone (wise) said "Code flows from dev to
prod, data should flow from prod to dev".
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"
===
Once this script is complete, the "galaxydev" database is an exact copy of
"galaxyprod" database (except queued jobs).
Hope this helps,
-gordon
___
Please keep all replies on the list by using "reply all"
in your mail client. To manage your subscriptions to this
and other Galaxy lists, please use the interface at:
http://lists.bx.psu.edu/
To search Galaxy mailing lists use the unified search at:
http://galaxyproject.org/search/mailinglists/
Re: [galaxy-dev] Production and Dev server on the same database.
On Mon, Mar 25, 2013 at 5:41 PM, Ted Goldstein wrote: > Hi > Does anyone have any experience or advice about running > two servers on the same database? > My advice is don't do it. The moment you update the development server to run with a new database schema version, your shared database will be updated, and the production server will probably stop working. That's assuming there are not other issues before that... (If you are talking about running two version-locked production servers, then perhaps there is precedent with load balancing systems which might be worth investigating) Peter ___ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at: http://lists.bx.psu.edu/ To search Galaxy mailing lists use the unified search at: http://galaxyproject.org/search/mailinglists/
