postgres

the drush (drupal.org/project/drush Drupal Shell) toolset provides some nice featured for moving database for drupal sites.

to see all the drush commands related to sql type: drush | grep sql

davidhazel@Macintosh-10:/workspacessd/example.local/sites/all/modules$ drush | grep sql

SQL commands: (sql) sql-cli (sqlc) Open a SQL command-line interface using Drupal's credentials. sql-connect A string for connecting to the DB. sql-drop Drop all tables in a given database. sql-dump Exports the Drupal DB as SQL using mysqldump or equivalent. sql-query (sqlq) Execute a query against the site database. sql-sync Copy and import source database to target database. Transfers via rsync.

From within the drupal tree do the following: for exporting the db snapshot from the source database

drush sql-dump

you'll get a file drush_nameofdatabase.sql.

for importing that file into your local database drush sql-connect

will give you the local db connection string

"psql --dbname=example --host=localhost --port=5432 --username=postgres" which you can use to import

so psql --dbname=example --host=localhost --port=5432 --username=postgres < drush_snp.sql

will load the file into your local database.

Whenever your building a new Drupal site or troubleshooting a problem on an existing site, it helps to have a local development copy to make it easier to do things like populate test data, try new modules, or run xdebug against.

It's pretty easy to move the code around using svn, rsync or my new favorite git. Moving the database is a bit more complicated.

I've gotten in the habit with the MySQL databases of exporting the database into a .sql file, committing it to git, pulling a copy local, then loading into my local MySQL instance.

You can of course do the same thing with the Postgres databases but it's a little different.

First off Backup and Migrate doesn't appear to work properly with PostGres, basically refusing to allow configuration of the source database.

This leaves a couple of options:

drush sql-dump / sql-connect or pg_dump

drush sql-dump is nice because it is db engine agnostic, making the appropriate adjustments based on the db your using.

However even with this method I was experiencing some Notice: unserialize() [function.unserialize]: Error at offset 0 of 22765 bytes in errors on my local machine.

After some hair pulling I found this bytea_output setting in postgresql.conf Thanks to this post http://postgresql.1045698.n5.nabble.com/Bytea-error-in-PostgreSQL-9-0-td... for pointing me in the right direction.

Lately I've been working a bit with Postgres and after many years using MySQL I'm finding it a bit challenging to make the syntax switch. One of the things that has helped is the vast resources online, but until it becomes second nature, I'm finding myself having to keep digging through those Postgres resources over and over. As a result, I've decided to start this running thread on command I keep using. They're here in no particular order.

bulk grant permissions to a user instead of having to do it table by table. GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;