Thread: Automating Postgres Backup

gcj wrote:
> I’m using Postgres 8.3 on Windows Server to store data from an
> Apache/Tomcat Web Application.  I’m using pg_dump to backup the database
> manually on a daily basis.
>
> Can someone recommend a solution or 3^rd party application to automate
> the backup process?  Ideally, I would like to do this without taking the
> application off-line.


I run PostgreSQL on a non-Windows system; but I understand that Windows
has something called "Scheduled Tasks" that should do what you want.

I use pgAgent packaged with PostgreSQL to schedule pg_dump backups.  Pg_dump doesn't require the db to be offline.  The nice thing about PgAgent is that it works
both on Linux and Windows.


Here is a quick primer on how to do it
http://www.postgresonline.com/journal/index.php?/archives/19-Setting-up-PgAgent-and-Doing-Scheduled-Backups.html

Hope that helps,
Regina


-----Original Message-----
From: pgsql-novice-owner@postgresql.org on behalf of gcj
Sent: Tue 11/11/2008 10:23 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Automating Postgres Backup

Hi,



I'm using Postgres 8.3 on Windows Server to store data from an Apache/Tomcat
Web Application.  I'm using pg_dump to backup the database manually on a
daily basis.



Can someone recommend a solution or 3rd party application to automate the
backup process?  Ideally, I would like to do this without taking the
application off-line.



Thanks,



gcj

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.

Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.

Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.

I take it from your path setting that you are running under Vista?  I've never tried PostgreSQL Agent under Vista though I run PgAdmin III fine from there and psql from there.

It sounds like your pgpass.conf is not working since you are still prompted for a password when you run your batch script.

Did you place your pgpass.conf file there or was it there to begin with?

On my Vista - my pgpass.conf got autogenerated by PgAdminIII and is located in

C:\Users\Administrator\AppData\Roaming\postgresql\pgpass.conf

So if you manually placed it, I wonder if its just in the wrong location.

Hope that helps,
Regina


-----Original Message-----
From: pgsql-novice-owner@postgresql.org on behalf of gcj
Sent: Tue 11/11/2008 10:18 PM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Automating Postgres Backup

Thank you very much for your suggestion.  I read the information in the link
you sent.



I installed pgAgent, got the Windows PostgreSQL Scheduling Agent service
running using an administrator account, created a pgpass.conf file with my
server's settings and password, and created a script file to run pg_dump.



Unfortunately, my pgAgent job shows "Last Result" as running, but never
creates the backup file.  I can run the script file manually by
double-clicking it - it prompts me for a password and after I enter it, I
get my pg_dump export file.



I'm not sure what I'm missing - my pgpass.conf file is in
c:\users\adminuser\AppData\postgresql\ and the scheduling agent service is
running.



Any suggestions on how to troubleshoot pgAgent?



Regards,



gcj







  _____ 

From: Obe, Regina [mailto:robe.dnd@cityofboston.gov]
Sent: Tuesday, November 11, 2008 8:27 AM
To: gcj
Subject: RE: [NOVICE] Automating Postgres Backup



I use pgAgent packaged with PostgreSQL to schedule pg_dump backups.  Pg_dump
doesn't require the db to be offline.


Here is a quick primer on how to do it
http://www.postgresonline.com/journal/index.php?/archives/19-Setting-up-PgAg
ent-and-Doing-Scheduled-Backups.html

Hope that helps,
Regina

-----Original Message-----
From: pgsql-novice-owner@postgresql.org on behalf of gcj
Sent: Tue 11/11/2008 10:23 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Automating Postgres Backup

Hi,



I'm using Postgres 8.3 on Windows Server to store data from an Apache/Tomcat
Web Application.  I'm using pg_dump to backup the database manually on a
daily basis.



Can someone recommend a solution or 3rd party application to automate the
backup process?  Ideally, I would like to do this without taking the
application off-line.



Thanks,



gcj




  _____ 

The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure pursuant to
Massachusetts law. It is intended solely for the addressee. If you received
this in error, please contact the sender and delete the material from any
computer.


  _____ 

Help make the earth a greener place. If at all possible resist printing this
email and join us in saving paper.

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.

Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.

Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.

Is it using the same account as you logged in with when you run the batch script?  If it isn't you'll need to copy the conf file into the directory for the service account.

To test log in to the computer using the postgres service account you have the service running under. 

Alternatively - which is what I usually do - I just have my PostgreSQL pg_hba.conf set to trust the ip of the computer pgagent is running in which in my case is always the same as the PostgreSQL server (so trust localhost).

To make sure its not some stupid thing with batch script - create a simple batch script that does nothing but output text.  Something like

dir > C:\dir.txt

That would require no permissions of any sort so should create a file on your c drive
if you right-click the job and choose -> Run Now

In fact you don't even need to create a batch script -
just create a job and a step -
make sure step is set to Kind->Batch 
and then just paste the above in the definition tab.




-----Original Message-----
From: pgsql-novice-owner@postgresql.org on behalf of gcj
Sent: Wed 11/12/2008 12:26 PM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Automating Postgres Backup

Thanks very much for your help.



I did have my pgpass.conf file in the wrong location.  I had pgAdminIII
create the password file and it put it in the correct directory.  Now, when
I manually execute my batch file (via a mouse click), the backup files are
created successfully and I do not get prompted to enter a password.



Despite this success, however, my pgAgent job does not execute.  The
scheduling agent service starts and is running.  The service is using an
administrator account, so I don't think it's a permissions / access problem.



Any other suggestions?



Regards,



gcj

  _____ 

From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Obe, Regina
Sent: Tuesday, November 11, 2008 10:54 PM
To: gcj; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Automating Postgres Backup



I take it from your path setting that you are running under Vista?  I've
never tried PostgreSQL Agent under Vista though I run PgAdmin III fine from
there and psql from there.

It sounds like your pgpass.conf is not working since you are still prompted
for a password when you run your batch script.

Did you place your pgpass.conf file there or was it there to begin with?

On my Vista - my pgpass.conf got autogenerated by PgAdminIII and is located
in

C:\Users\Administrator\AppData\Roaming\postgresql\pgpass.conf

So if you manually placed it, I wonder if its just in the wrong location.

Hope that helps,
Regina


-----Original Message-----
From: pgsql-novice-owner@postgresql.org on behalf of gcj
Sent: Tue 11/11/2008 10:18 PM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Automating Postgres Backup

Thank you very much for your suggestion.  I read the information in the link
you sent.



I installed pgAgent, got the Windows PostgreSQL Scheduling Agent service
running using an administrator account, created a pgpass.conf file with my
server's settings and password, and created a script file to run pg_dump.



Unfortunately, my pgAgent job shows "Last Result" as running, but never
creates the backup file.  I can run the script file manually by
double-clicking it - it prompts me for a password and after I enter it, I
get my pg_dump export file.



I'm not sure what I'm missing - my pgpass.conf file is in
c:\users\adminuser\AppData\postgresql\ and the scheduling agent service is
running.



Any suggestions on how to troubleshoot pgAgent?



Regards,



gcj







  _____

From: Obe, Regina [mailto:robe.dnd@cityofboston.gov]
Sent: Tuesday, November 11, 2008 8:27 AM
To: gcj
Subject: RE: [NOVICE] Automating Postgres Backup



I use pgAgent packaged with PostgreSQL to schedule pg_dump backups.  Pg_dump
doesn't require the db to be offline.


Here is a quick primer on how to do it
http://www.postgresonline.com/journal/index.php?/archives/19-Setting-up-PgAg
ent-and-Doing-Scheduled-Backups.html

Hope that helps,
Regina

-----Original Message-----
From: pgsql-novice-owner@postgresql.org on behalf of gcj
Sent: Tue 11/11/2008 10:23 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Automating Postgres Backup

Hi,



I'm using Postgres 8.3 on Windows Server to store data from an Apache/Tomcat
Web Application.  I'm using pg_dump to backup the database manually on a
daily basis.



Can someone recommend a solution or 3rd party application to automate the
backup process?  Ideally, I would like to do this without taking the
application off-line.



Thanks,



gcj




  _____

The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure pursuant to
Massachusetts law. It is intended solely for the addressee. If you received
this in error, please contact the sender and delete the material from any
computer.


  _____

Help make the earth a greener place. If at all possible resist printing this
email and join us in saving paper.





  _____ 

The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure pursuant to
Massachusetts law. It is intended solely for the addressee. If you received
this in error, please contact the sender and delete the material from any
computer.


  _____ 

Help make the earth a greener place. If at all possible resist printing this
email and join us in saving paper.

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.

Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.