Thread: Re: automating backup ?

Re: automating backup ?

From
"Harvey, Allan AC"
Date:
Start->Control Panel->Scheduled Tasks

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Zlatko Matic
> Sent: Monday, 27 June 2005 9:21 AM
> To: Michael Fuhr
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] automating backup ?
>
>
> Thanks Fuhr.
> Anybody can tell me how to do it on Windows XP ?
> Thanks.
>
> ----- Original Message -----
> From: "Michael Fuhr" <mike@fuhr.org>
> To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>
> Cc: <pgsql-general@postgresql.org>
> Sent: Sunday, June 26, 2005 2:59 PM
> Subject: Re: [GENERAL] automating backup ?
>
>
> > On Sun, Jun 26, 2005 at 11:18:31AM +0200, Zlatko Matic wrote:
> >>
> >> How to automate backup, so that Postgres automatically backups, for
> >> example, once in a week ?
> >
> > Using the operating system's mechanism for scheduling jobs to run
> > periodically.  For example, cron on Unix-like systems.
> >
> >> The same question about vacuum ?
> >
> > Same answer as above.  See also contrib/pg_autovacuum.
> >
> >> Concerning backup, how to prevent that someone makes a
> copy (for example
> >> pg_dumpall) of a database, then installs new instance of
> Postgres, create
> >> the same user acount that was the original owner and then
> restore the
> >> database. In that case all restrictions would be overriden, right ?
> >
> > All what restrictions?  If you've granted users permission to select
> > certain data, then they can copy that data to somewhere else and
> > do whatever they like with it; but with the original data they can
> > do only what you grant them permission to do.  If you don't want
> > users to copy data then don't grant them select privilege on it,
> > and make sure they don't have database superuser or operating system
> > superuser (administrator) privileges on the database server.
> >
> > --
> > Michael Fuhr
> > http://www.fuhr.org/~mfuhr/
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

Re: automating backup ?

From
"Magnus Hagander"
Date:
> 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

Re: automating backup ?

From
"Zlatko Matic"
Date:
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@sollentuna.net>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>; "Andreas" <maps.on@gmx.net>;
<pgsql-general@postgresql.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


Re: automating backup ?

From
"Relyea, Mike"
Date:
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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Zlatko Matic
Sent: Tuesday, June 28, 2005 9:08 AM
To: Magnus Hagander; Andreas; pgsql-general@postgresql.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@sollentuna.net>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>; "Andreas"
<maps.on@gmx.net>;
<pgsql-general@postgresql.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

Re: automating backup ?

From
Andreas
Date:
Zlatko Matic schrieb:

> 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..


you have to create the sub-dir and the file yourself


Re: automating backup ?

From
Douglas McNaught
Date:
"Zlatko Matic" <zlatko.matic1@sb.t-com.hr> writes:

> 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..

I'm pretty sure it's not created by the default install (it certainly
isn't on Unix)--you need to create it yourself if you're going to use
it.

-Doug

Re: automating backup ?

From
"Zlatko Matic"
Date:
I would appreciate some example.
Thanks.

----- Original Message -----
From: "Relyea, Mike" <Mike.Relyea@xerox.com>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>;
<pgsql-general@postgresql.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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Zlatko Matic
Sent: Tuesday, June 28, 2005 9:08 AM
To: Magnus Hagander; Andreas; pgsql-general@postgresql.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@sollentuna.net>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>; "Andreas"
<maps.on@gmx.net>;
<pgsql-general@postgresql.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


Re: automating backup ?

From
"Relyea, Mike"
Date:
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.matic1@sb.t-com.hr]
Sent: Tuesday, June 28, 2005 1:07 PM
To: Relyea, Mike; pgsql-general@postgresql.org
Subject: Re: [GENERAL] automating backup ?

I would appreciate some example.
Thanks.

----- Original Message -----
From: "Relyea, Mike" <Mike.Relyea@xerox.com>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>;
<pgsql-general@postgresql.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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Zlatko Matic
Sent: Tuesday, June 28, 2005 9:08 AM
To: Magnus Hagander; Andreas; pgsql-general@postgresql.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@sollentuna.net>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>; "Andreas"
<maps.on@gmx.net>;
<pgsql-general@postgresql.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


Re: automating backup ?

From
"Zlatko Matic"
Date:
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.Relyea@xerox.com>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>;
<pgsql-general@postgresql.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.matic1@sb.t-com.hr]
Sent: Tuesday, June 28, 2005 1:07 PM
To: Relyea, Mike; pgsql-general@postgresql.org
Subject: Re: [GENERAL] automating backup ?

I would appreciate some example.
Thanks.

----- Original Message -----
From: "Relyea, Mike" <Mike.Relyea@xerox.com>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>;
<pgsql-general@postgresql.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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Zlatko Matic
Sent: Tuesday, June 28, 2005 9:08 AM
To: Magnus Hagander; Andreas; pgsql-general@postgresql.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@sollentuna.net>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>; "Andreas"
<maps.on@gmx.net>;
<pgsql-general@postgresql.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


Re: automating backup ?

From
Andreas
Date:
Zlatko Matic schrieb:

> 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 ?


Perhaps its like this.
pg_dumpall wants to dump the whole database-cluster (every database in
your server) that is not only "MONITORINGZ" but the two templates, too.
So pg_dumpall doesn't ask you for the password to your own database but
2 times for the pw for the 2 template DBs.

In pgpass.conf write   *   instead of MONITORINGZ or copy the line for
template0 and template1.
Or don't use pg_dumpall and use pg_dump instead just for MONITORINGZ.

Maybe it's somerthing else ... one never knows with those computers ... ;)


Re: automating backup ?

From
"Zlatko Matic"
Date:
Hi.
Yes, you were right. I added lines for template0 and template1 into
pgpass.conf file and now it works.

I'm wondering how to include timestamp in backup file name ?

----- Original Message -----
From: "Andreas" <maps.on@gmx.net>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, June 29, 2005 1:56 AM
Subject: Re: [GENERAL] automating backup ?


> Zlatko Matic schrieb:
>
>> 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 ?
>
>
> Perhaps its like this.
> pg_dumpall wants to dump the whole database-cluster (every database in
> your server) that is not only "MONITORINGZ" but the two templates, too.
> So pg_dumpall doesn't ask you for the password to your own database but 2
> times for the pw for the 2 template DBs.
>
> In pgpass.conf write   *   instead of MONITORINGZ or copy the line for
> template0 and template1.
> Or don't use pg_dumpall and use pg_dump instead just for MONITORINGZ.
>
> Maybe it's somerthing else ... one never knows with those computers ... ;)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


Re: automating backup ?

From
"Zlatko Matic"
Date:
Mike, you have:
pg_dump -f Name-Of-My-Dump-File -Fc -Z 9 -h localhost -U myUserName myDBname

How do you exactly restore it ? By pg_restore or psql ? What parameters ?

----- Original Message -----
From: "Relyea, Mike" <Mike.Relyea@xerox.com>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>;
<pgsql-general@postgresql.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.matic1@sb.t-com.hr]
Sent: Tuesday, June 28, 2005 1:07 PM
To: Relyea, Mike; pgsql-general@postgresql.org
Subject: Re: [GENERAL] automating backup ?

I would appreciate some example.
Thanks.

----- Original Message -----
From: "Relyea, Mike" <Mike.Relyea@xerox.com>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>;
<pgsql-general@postgresql.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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Zlatko Matic
Sent: Tuesday, June 28, 2005 9:08 AM
To: Magnus Hagander; Andreas; pgsql-general@postgresql.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@sollentuna.net>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>; "Andreas"
<maps.on@gmx.net>;
<pgsql-general@postgresql.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


Explain Analyse never returns .. maybe a bug

From
David Gagnon
Date:
Hi all,

  I stop the following statement after 3 minutes....

   explain analyse select *
        From IC
        where IC.ICNUM NOT IN (select IRICNUM from IR WHERE IRSTATUT in
(1, 2))


I'm using 8.0.3 on windows.

Is that a know bug ?

Thanks
/David

P.S.: I can send more info if needed.

Re: Explain Analyse never returns .. maybe a bug

From
"Sean Davis"
Date:
----- Original Message -----
From: "David Gagnon" <dgagnon@siunik.com>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, June 29, 2005 9:25 AM
Subject: [GENERAL] Explain Analyse never returns .. maybe a bug


> Hi all,
>
>  I stop the following statement after 3 minutes....
>
>   explain analyse select *
>        From IC
>        where IC.ICNUM NOT IN (select IRICNUM from IR WHERE IRSTATUT in (1,
> 2))
>
>
> I'm using 8.0.3 on windows.
> Is that a know bug ?

David,

Remember that EXPLAIN ANALYZE actually RUNS the query.  It may actually be
taking 3+ minutes to run the query.  How large are the tables?  What does
EXPLAIN (without ANALYZE) say?  Have you run the query without EXPLAIN
ANALYZE to know how long it normally takes--what is the basis for suggesting
that 3 minutes is "too long"?

Sean



Re: Explain Analyse never returns .. maybe a bug

From
Michael Fuhr
Date:
On Wed, Jun 29, 2005 at 09:25:24AM -0400, David Gagnon wrote:
>
>  I stop the following statement after 3 minutes....
>
>   explain analyse select *
>        From IC
>        where IC.ICNUM NOT IN (select IRICNUM from IR WHERE IRSTATUT in
> (1, 2))

What does EXPLAIN without ANALYZE show?  What are the table definitions
and what are the results of the following queries?

SELECT COUNT(*) FROM IC;
SELECT COUNT(*) FROM IR;
SELECT COUNT(*) FROM IR WHERE IRSTATUT IN (1, 2);
SELECT COUNT(DISTINCT IRICNUM) FROM IR WHERE IRSTATUT IN (1, 2);

Have you run VACUUM ANALYZE on the tables?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Explain Analyse never returns .. maybe a bug

From
Douglas McNaught
Date:
David Gagnon <dgagnon@siunik.com> writes:

> Hi all,
>
>   I stop the following statement after 3 minutes....
>
>    explain analyse select *
>         From IC
>         where IC.ICNUM NOT IN (select IRICNUM from IR WHERE IRSTATUT
>         in (1, 2))
>
>
> I'm using 8.0.3 on windows.
>
> Is that a know bug ?

Post your schema, and the sizes of the tables in question.

Also, a regular EXPLAIN (without ANALYZE) should print the query
plan that PG has chosen--post that too.

-Doug

Re: Explain Analyse never returns .. maybe a bug

From
David Gagnon
Date:
Oups !  Sorry I misunderstanding the command .. always thought it was
almos instantaneous.... my mistake.. really sorry about that

Thanks :-)

/David


Sean Davis wrote:

>
> ----- Original Message ----- From: "David Gagnon" <dgagnon@siunik.com>
> Cc: <pgsql-general@postgresql.org>
> Sent: Wednesday, June 29, 2005 9:25 AM
> Subject: [GENERAL] Explain Analyse never returns .. maybe a bug
>
>
>> Hi all,
>>
>>  I stop the following statement after 3 minutes....
>>
>>   explain analyse select *
>>        From IC
>>        where IC.ICNUM NOT IN (select IRICNUM from IR WHERE IRSTATUT
>> in (1, 2))
>>
>>
>> I'm using 8.0.3 on windows.
>> Is that a know bug ?
>
>
> David,
>
> Remember that EXPLAIN ANALYZE actually RUNS the query.  It may
> actually be taking 3+ minutes to run the query.  How large are the
> tables?  What does EXPLAIN (without ANALYZE) say?  Have you run the
> query without EXPLAIN ANALYZE to know how long it normally takes--what
> is the basis for suggesting that 3 minutes is "too long"?
>
> Sean
>
>
>