Thread: Re: automating backup ?
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 >
> 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
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
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
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
"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
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
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
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
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 ... ;)
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)
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
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.
----- 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
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/
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
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 > > >