0

I have two machines:

  • Remote, Ubuntu server, example.com, database: staging (owner user2, pass2), passwordless login for user1 (sudoer) via ssh
  • Local, Ubuntu, localhost, database: local (owner user3, pass3)

The question is:

  • How to import data and structure of staging database to local (and vice versa) in one command/script?
  • How to set up the permissions?

These are not production databases, so I don't need real time sync. I just need to automate the psql tasks (usually done by go to phppgadmin, export the data to file, import the data from file via psql, all from Local machine).

The best solution would be database owner/password independent, because I have few pairs more of such a databases. I have a root access on both machines and I can install any additional required users accounts/software.

takeshin
  • 1,471
  • 3
  • 21
  • 28
  • 1
    Either use the new replication features in 9.x or perform regular backup from production and restore to your staging server. – Alex Holst Nov 16 '10 at 17:29

1 Answers1

1

Might be simple enough as just scripting the database dumping and then transferring the file to the other database server and then restoring:

The 'pg_dump' utility found with the PostgreSQL Server can be utilized by Database Administrators for taking dumps or backups of databases into a file. The syntax of pg_dump is :

pg_dump databasename -f outputfile -i -x -O -R -S username

I am not a fan completely of this script http://www.wisdombay.com/articles/pg_dmp.sh but it should work just fine for you.

The reason for my caution is - because it houses your login/passwords

Now simply scp to the backup server and run a cron job to import the databases.

If stuck - let me know.

Community
  • 1
Glenn Kelley
  • 1,294
  • 6
  • 10
  • Thanks. I've seen this script before, but I think plain text passwords are not a good solution at all. Maybe some setup in sudoers file to run this script is needed. But I think, when I already have a passwordless access with ssh, I might run some commands just as a parameter for ssh. But what commands? ;) – takeshin Nov 16 '10 at 20:20
  • 1
    Use .pgpass to store your password, that's safe. http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html – Frank Heikens Nov 18 '10 at 13:05