Re: automating backup ?

From: "Zlatko Matic" <zlatko(dot)matic1(at)sb(dot)t-com(dot)hr>
To: "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: automating backup ?
Date: 2005-06-28 20:16:25
Message-ID: 003301c57c1e$552728e0$0b7c8353@zlatko58zaczpv
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Now I have pgpass.conf file in D:\Documents and Settings\Zlatko\Application
Data\postgresql
content of pgpass.conf is:
localhost:*:MONITORINGZ:postgres:tralalala

content of backup_script.bat is:
cd D:\Program Files\PostgreSQL\8.0\bin
pg_dumpall >D:\MONITORINGZ_DUMPALL -U postgres

still prompts for password...What is wrong ?

----- Original Message -----
From: "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)com>
To: "Zlatko Matic" <zlatko(dot)matic1(at)sb(dot)t-com(dot)hr>;
<pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, June 28, 2005 7:55 PM
Subject: RE: [GENERAL] automating backup ?

1) Create the directory %APPDATA%\postgresql
in my case it's C:\Documents and
Settings\Administrator\Application Data\postgresql
2) Create the file %APPDATA%\postgresql\pgpass.conf
I created it with Notepad
3) Put the necessary information into %APPDATA%\postgresql\pgpass.conf
I put one line in mine -
localhost:*:myDBname:myUserName:myPassword
4) Create the batch file to run your backup command
In my case, it reads:
"C:\Program Files\PostgreSQL\8.0\bin\psql" -h localhost -d myDBname -U
myUserName -f Name-Of-File-With-Maintenance-Commands
"C:\Program Files\PostgreSQL\8.0\bin\pg_dump" -f Name-Of-My-Dump-File
-Fc -Z 9 -h localhost -U myUserName myDBname

5) Use the task scheduler to run your newly created batch file whenever
you'd like it to run

I actually run my batch file every night. My DB has no activity during
the night, so I run my maintenance then.
Name-Of-File-With-Maintenance-Commands contains SQL to refresh a
materialized view and do a vacuum full analyze

-----Original Message-----
From: Zlatko Matic [mailto:zlatko(dot)matic1(at)sb(dot)t-com(dot)hr]
Sent: Tuesday, June 28, 2005 1:07 PM
To: Relyea, Mike; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] automating backup ?

I would appreciate some example.
Thanks.

----- Original Message -----
From: "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)com>
To: "Zlatko Matic" <zlatko(dot)matic1(at)sb(dot)t-com(dot)hr>;
<pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, June 28, 2005 3:56 PM
Subject: Re: [GENERAL] automating backup ?

That's because they don't exist. You need to create them. I did it on
WinXP and it works fine.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Zlatko Matic
Sent: Tuesday, June 28, 2005 9:08 AM
To: Magnus Hagander; Andreas; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] automating backup ?
Importance: High

Hi.
I can't find pgpass.conf file. It should be in Application Data
subdirectory, but there is no PostgreSQL subdirectory in Application
Data
directory (!?). I couldn't find pgpass.conf even by searching the hard
disk..

Regards,
Zlatko

----- Original Message -----
From: "Magnus Hagander" <mha(at)sollentuna(dot)net>
To: "Zlatko Matic" <zlatko(dot)matic1(at)sb(dot)t-com(dot)hr>; "Andreas"
<maps(dot)on(at)gmx(dot)net>;
<pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, June 28, 2005 10:16 AM
Subject: Re: [GENERAL] automating backup ?

> Hello.
> I created a Windows XP schedule for backup, following your
> instruction. Now I have a .bat file with this script:
>
> cd D:\Program Files\PostgreSQL\8.0\bin
> pg_dumpall >D:\MYDATABASE_DUMPALL -U postgres pg_dumpall
> >D:\MYDATABASE_SHEMA -U postgres -s pg_dumpall
> >D:\MYDATABASE_GLOBALS -U postgres -g
>
> Well, it works OK, but prompts for password every time. Is
> there any way that I pass the superuser password (off course,
> in safe way) so that it works automatically without prompting
> for password ?

Use a pgpass.conf file:
http://www.postgresql.org/docs/8.0/static/libpq-pgpass.html (remember
the file has to be in the profile of the account that executes the step.
And be sure to protect it with file system ACLs so other users can't
read it)

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim C. Nasby 2005-06-28 20:49:18 Re: Populating huge tables each day
Previous Message Tony Caduto 2005-06-28 19:39:59 8.1 Out parameter question