Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    6

    Unanswered: Single database recovery in continuous archive with PITR.

    I am new to postgres administration and I am trying to determine the best way to configure a multi-project postgres server. In my research so far it looks like continuous archive allows you to recover all of the databases and only ALL of them. Our requirements are that we need to do PITR, but since this server will host multiple projects we have to be recover databases to a certain point in time on an individual basis.

    Is the typical approach for this to start multiple postgres pointed at different init directories so you can recover them on an individual basis as needed?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    You are right: PITR can only be used for the whole database cluster.

    If you need to be able to restore each project separately you will have to use multiple Postgres instances (same binaries, running against different data directories on different ports)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

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

  3. #3
    Join Date
    Dec 2004
    Posts
    6
    Quote Originally Posted by shammat View Post
    You are right: PITR can only be used for the whole database cluster.

    If you need to be able to restore each project separately you will have to use multiple Postgres instances (same binaries, running against different data directories on different ports)
    Thank you so much Shammat. That is what my research lead me to believe. I wish the docs would make that explicit as what needs to be done in the backup and recovery sections as I would think that would be a pretty common configuration. Maybe it is in the docs somewhere and I missed it, but I searched for a couple of days without success. My small group(2 people) have to maintain multiple instances of 7 flavors of DBMS and there is never time enough to become a thorough expect on all of them.

    Again, Thanks!

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by jabarry View Post
    T I wish the docs would make that explicit as what needs to be done in the backup and recovery sections as I would think that would be a pretty common configuration.
    It's there. But you are right, it could be more explicit.

    In the chapter "Recovering Using a Continuous Archive Backup" one of the first steps in doing a PITR is "Remove all existing files and subdirectories under the cluster data directory" which essentially says that you overwrite everything during the restore.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

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

  5. #5
    Join Date
    Dec 2004
    Posts
    6
    Quote Originally Posted by shammat View Post
    It's there. But you are right, it could be more explicit.

    In the chapter "Recovering Using a Continuous Archive Backup" one of the first steps in doing a PITR is "Remove all existing files and subdirectories under the cluster data directory" which essentially says that you overwrite everything during the restore.
    Yeah, that is what I was getting at - hit me over the head with it please . I read that and it helped to figure it out. But not with any degree of confidence that I was pursing a good way to do the configuration we desired. Thanks again for your clarification!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •