On 01/25/2011 10:25 AM, Hans-Rudolf Hotz wrote:
On Jan 25, 2011, at 3:40 PM, Hans-Rudolf Hotz wrote:
[...]
Once again be careful with what you are doing, and don't blame me if you
create a mess ;)


-1-
PgAdmin ( http://www.pgadmin.org/ ) is a GUI application to manage Postgres 
databases - it might be a friendlier option than running direct SQL queries 
with 'psql'.

-2-
Before directly manipulation your DB, you can/should back it up with the 
following command:
$ pg_dump -U USER DB > galaxy_db.sql

If something does go wrong, you can at least revert the data to a valid state.

-3-
I use the following script to replicate the production database to the 
development database, and then it's easier to experiment with any SQL command 
you want without affecting the production server:
=======
#!/bin/sh

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

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

DEST=/home/gordon/projects/galaxy_db_backups/
FILE="${DEST}${FILE}"

echo "Dumping Prod-DB to:"
echo "  $FILE"
echo "(Enter Galaxyprod Password)"
pg_dump -c -U galaxyprod galaxyprod | sed 's/galaxyprod/galaxydevel/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
echo "(Enter GalaxyDevel Password)":
zcat "$FILE" | psql -U galaxydevel galaxydevel || exit 1
======================

The "pg_dump -c" will add the SQL commands to drop and re-create the tables, so all 
tables in "galaxydevel" database are an exact copy.

-gordon
_______________________________________________
galaxy-dev mailing list
galaxy-dev@lists.bx.psu.edu
http://lists.bx.psu.edu/listinfo/galaxy-dev

Reply via email to