#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0

    Question Back-up of postGis databases


    Hello

    In our company we use a lot of different PostgreSQL databases with the PostGIS extension. Now it's my task to back-up all those databases. I was thinking to write a script to back-up the databases individually, so when something go wrong the admin can restore that database without problems. Do some of you have some suggestion how I can handle this the best. witch command can i use the best and witch format and so on. This is for me the first time that I need to work with this kind of databases.

    Thanks!
    Dieter
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    You did read the "Backup and Restore" chapter in the manual, did you?

    http://www.postgresql.org/docs/current/static/backup.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0
    Yes I have read this manual. But if I just use pg_dump is it possible to do this remote on a running database. Because some databases are critical an can't be locked or turned off. And I read about WAL but this use extra memory I suppose. The best situation is that there is a script on sort of backup-server that can connect to the database. make a dump and save this dump to the backupmedium of course it will delete older backups and so on. But we need to be sure that the backup is consistent.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    Originally Posted by jackiejarvis
    But if I just use pg_dump is it possible to do this remote on a running database. Because some databases are critical an can't be locked or turned off.
    I don't understand that sentence.


    [quote]But we need to be sure that the backup is consistent./QUOTE]Then why don't you use pg_dump:
    Originally Posted by The fine manual
    Dumps created by pg_dump are internally consistent, meaning, the dump represents a snapshot of the database at the time pg_dump began running. pg_dump does not block other operations on the database while it is working
    Taken from: http://www.postgresql.org/docs/current/static/backup-dump.html
    (Emphasis mine)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo