Обсуждение: New feature: selectivity - new attribute in function
Hi. It is sometimes hard to tune complicated queries. Maybe add new attribute to functions returning boolean - selectivity, defining how big is percent of rows for which the function returns true. 2011/4/15, Edison So <edison.so2@gmail.com>: > I have a DELL server running Windows server 2003 and Postgres 8.1. > > I used pg_dump to back up a database test: > > pg_dump -v -h localhost -p 5432 -U postgres -F c -b -f > "D:/db_dump/backup.bak" test > > The backup was showing the following error. > . > pg_dump: dumping contents of table history > pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- > ftell used > > pg_dump: dumping contents of table history_archive > > pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- > ftell used > > pg_dump: dumping contents of table historymetadata > > pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- > ftell used > > I was trying to do a Posgres 8.1.4 backup and restored it to Postgres 9.0 on > a new Dell server running Windows server 2008. > > Any clue how to resolve it? > > Edison > > -- > Edison > -- ------------ pasman
Thank you for the reply.
It has nothing to do with programming. I was trying to back up the 8.1 database (using pg_dump) which had exceeded 2Gig limit according to a response. When I tried to restore it using pg_restore to a 9.0 database, it complained about custom archiver error and corruption and the whole backup stopped.
One response told me to try backing up 8.1 database using 9.0 pg_dump and restore it to 9.0 database using 9.0 pg_restore. Another option, I can think of, is to back up each table one at time and restore it one by one. This way the 2G limit will never come up during the backup. However, it requires a lot of works.
Any suggestion?
Thanks,
Thanks,
2011/4/16 pasman pasmański <pasman.p@gmail.com>
Hi.
It is sometimes hard to tune complicated queries. Maybe add new
attribute to functions returning boolean - selectivity, defining how
big is percent of rows for which the function returns true.
2011/4/15, Edison So <edison.so2@gmail.com>:
> I have a DELL server running Windows server 2003 and Postgres 8.1.
>
> I used pg_dump to back up a database test:
>
> pg_dump -v -h localhost -p 5432 -U postgres -F c -b -f
> "D:/db_dump/backup.bak" test
>
> The backup was showing the following error.
> .
> pg_dump: dumping contents of table history
> pg_dump: [custom archiver] WARNING: ftell mismatch with expected position --
> ftell used
>
> pg_dump: dumping contents of table history_archive
>
> pg_dump: [custom archiver] WARNING: ftell mismatch with expected position --
> ftell used
>
> pg_dump: dumping contents of table historymetadata
>
> pg_dump: [custom archiver] WARNING: ftell mismatch with expected position --
> ftell used
>
> I was trying to do a Posgres 8.1.4 backup and restored it to Postgres 9.0 on
> a new Dell server running Windows server 2008.
>
> Any clue how to resolve it?
>
> Edison
>
> --
> Edison
>
--
------------
pasman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Edison
On 16 Apr 2011, at 22:10, Edison So wrote: > One response told me to try backing up 8.1 database using 9.0 pg_dump and restore it to 9.0 database using 9.0 pg_restore. You shouldn't try to restore into a newer database version with a dump made with an older pg_dump. Pg_dump isn't (and can'tbe) forward compatible to database versions that don't yet exist when it is released. *Always* use a dump made with pg_dump from the newer database if you're upgrading. Especially between major versions of thedatabase. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4da9fae2651344328412467!
Hello Alban,
Thank you for the suggestion.
May I ask you and others another advice as to taking a Postgres course?
I am not a DBA and have never taken a database course. However, I am using Postgres for a system I am supporting. Any suggestion? I need to set up a primary-standby or load-balancing Postgres system for disaster recovery.
I am not a DBA and have never taken a database course. However, I am using Postgres for a system I am supporting. Any suggestion? I need to set up a primary-standby or load-balancing Postgres system for disaster recovery.
Thanks in advance.
I would like to take an instructor-led Postgres 9.0 course which includes database administration and replication with Slony.
On Sat, Apr 16, 2011 at 4:23 PM, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:
On 16 Apr 2011, at 22:10, Edison So wrote:You shouldn't try to restore into a newer database version with a dump made with an older pg_dump. Pg_dump isn't (and can't be) forward compatible to database versions that don't yet exist when it is released.
> One response told me to try backing up 8.1 database using 9.0 pg_dump and restore it to 9.0 database using 9.0 pg_restore.
*Always* use a dump made with pg_dump from the newer database if you're upgrading. Especially between major versions of the database.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:1251,4da9fad7651346015739574!
--
Edison
On Saturday, April 16, 2011 1:10:39 pm Edison So wrote: > Thank you for the reply. > > It has nothing to do with programming. I was trying to back up the 8.1 > database (using pg_dump) which had exceeded 2Gig limit according to a > response. When I tried to restore it using pg_restore to a 9.0 database, it > complained about custom archiver error and corruption and the whole backup > stopped. > > One response told me to try backing up 8.1 database using 9.0 pg_dump and > restore it to 9.0 database using 9.0 pg_restore. Another option, I can > think of, is to back up each table one at time and restore it one by one. > This way the 2G limit will never come up during the backup. However, it > requires a lot of works. If you are trying to restore forward to 9.0 you will need to use the 9.0 version of pg_dump in either case. If you are trying to create a backup to use on the 8.1 database you might try -Fp instead of -Fc to create the dump(untested). The bug seems to be with the binary dump version not the text version. > > Any suggestion? > > Thanks, > -- Adrian Klaver adrian.klaver@gmail.com
On Saturday, April 16, 2011 1:10:39 pm Edison So wrote: > Thank you for the reply. > > It has nothing to do with programming. I was trying to back up the 8.1 > database (using pg_dump) which had exceeded 2Gig limit according to a > response. When I tried to restore it using pg_restore to a 9.0 database, it > complained about custom archiver error and corruption and the whole backup > stopped. > > One response told me to try backing up 8.1 database using 9.0 pg_dump and > restore it to 9.0 database using 9.0 pg_restore. Another option, I can > think of, is to back up each table one at time and restore it one by one. > This way the 2G limit will never come up during the backup. However, it > requires a lot of works. > > Any suggestion? > Should have added to my previous post that the -Fp option creates a file that is not compressed. -- Adrian Klaver adrian.klaver@gmail.com
Hello Adrian,
Thank you for your reply again.
I will try to use your previous suggestion that I will backup 8.1 database using 9.0 pg_dump and restore it to 9.0 database. Hopefully, 9.0 pg_dump will work on 8.1 database. I have never taken a DB course and very very new to Postgres.
Thanks again,
2011/4/16 Adrian Klaver <adrian.klaver@gmail.com>
On Saturday, April 16, 2011 1:10:39 pm Edison So wrote:If you are trying to restore forward to 9.0 you will need to use the 9.0 version
> Thank you for the reply.
>
> It has nothing to do with programming. I was trying to back up the 8.1
> database (using pg_dump) which had exceeded 2Gig limit according to a
> response. When I tried to restore it using pg_restore to a 9.0 database, it
> complained about custom archiver error and corruption and the whole backup
> stopped.
>
> One response told me to try backing up 8.1 database using 9.0 pg_dump and
> restore it to 9.0 database using 9.0 pg_restore. Another option, I can
> think of, is to back up each table one at time and restore it one by one.
> This way the 2G limit will never come up during the backup. However, it
> requires a lot of works.
of pg_dump in either case. If you are trying to create a backup to use on the
8.1 database you might try -Fp instead of -Fc to create the dump(untested). The
bug seems to be with the binary dump version not the text version.
>
> Any suggestion?
>
> Thanks,
>
--
Adrian Klaver
adrian.klaver@gmail.com
--
Edison
On Saturday, April 16, 2011 1:59:48 pm Edison So wrote: > Hello Adrian, > > Thank you for your reply again. > > I will try to use your previous suggestion that I will backup 8.1 database > using 9.0 pg_dump and restore it to 9.0 database. Hopefully, 9.0 pg_dump > will work on 8.1 database. I have never taken a DB course and very very new > to Postgres. Well the docs say it can work back to 7.0:) > > Thanks again, > -- Adrian Klaver adrian.klaver@gmail.com
Haha. Having a QA background for many years, I do not believe in anything until it is tested. :)
Thanks.
On Sat, Apr 16, 2011 at 5:02 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On Saturday, April 16, 2011 1:59:48 pm Edison So wrote:Well the docs say it can work back to 7.0:)
> Hello Adrian,
>
> Thank you for your reply again.
>
> I will try to use your previous suggestion that I will backup 8.1 database
> using 9.0 pg_dump and restore it to 9.0 database. Hopefully, 9.0 pg_dump
> will work on 8.1 database. I have never taken a DB course and very very new
> to Postgres.
>
> Thanks again,
>
--
--
Edison
Oh yeah.
Forgot to mention that Postgres 9.0 does not have an option to restore text based back up file. Do you know how?
On Sat, Apr 16, 2011 at 5:06 PM, Edison So <edison.so2@gmail.com> wrote:
Haha. Having a QA background for many years, I do not believe in anything until it is tested. :)Thanks.--On Sat, Apr 16, 2011 at 5:02 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:On Saturday, April 16, 2011 1:59:48 pm Edison So wrote:Well the docs say it can work back to 7.0:)
> Hello Adrian,
>
> Thank you for your reply again.
>
> I will try to use your previous suggestion that I will backup 8.1 database
> using 9.0 pg_dump and restore it to 9.0 database. Hopefully, 9.0 pg_dump
> will work on 8.1 database. I have never taken a DB course and very very new
> to Postgres.
>
> Thanks again,
>
--
Edison
--
Edison
On Saturday, April 16, 2011 2:06:44 pm Edison So wrote: > Haha. Having a QA background for many years, I do not believe in anything > until it is tested. :) I have done it enough times to know it works. The issue that might arise are not related to the pg_dump program but version changes in behavior. In your case going from 8.1 to 9.0 goes through the stricter typing introduced in 8.3. The data will restore to the new version but you may run into problems when you do things in the database. Some time with the release notes for each version you are going through would be instructive. > > Thanks. Adrian Klaver adrian.klaver@gmail.com
On Saturday, April 16, 2011 2:07:48 pm Edison So wrote: > Oh yeah. > > Forgot to mention that Postgres 9.0 does not have an option to restore text > based back up file. Do you know how? > It is the same for all versions. psql (options) -f text_dump.sql -- Adrian Klaver adrian.klaver@gmail.com
On 16/04/2011 22:07, Edison So wrote: > Oh yeah. > Forgot to mention that Postgres 9.0 does not have an option to restore > text based back up file. Do you know how? Surely: psql -f <dump file> <database name> Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Saturday, April 16, 2011 2:07:48 pm Edison So wrote: > Oh yeah. > > Forgot to mention that Postgres 9.0 does not have an option to restore text > based back up file. Do you know how? > Just realized, if you are using the 9.0 version of pg_dump you can do a binary(- Fc) dump against the 8.1 server. The restriction applies to versions of pg_dump less than 8.2.9. -- Adrian Klaver adrian.klaver@gmail.com
I did that with Postgres 8.1 backup file on 9.0 database. I got continuous error messages like "Can not execute the command...". Well, I do not need worry about it for I am going to back up 8.1 database using 9.0 pg_dump command.
Let's see how it goes.
Thanks,
On Sat, Apr 16, 2011 at 5:17 PM, Raymond O'Donnell <rod@iol.ie> wrote:
On 16/04/2011 22:07, Edison So wrote:Surely:Oh yeah.
Forgot to mention that Postgres 9.0 does not have an option to restore
text based back up file. Do you know how?
psql -f <dump file> <database name>
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
--
Edison
On Saturday, April 16, 2011 4:05:52 pm Edison So wrote: > I did that with Postgres 8.1 backup file on 9.0 database. I got continuous > error messages like "Can not execute the command...". Well, I do not need > worry about it for I am going to back up 8.1 database using 9.0 pg_dump > command. > > Let's see how it goes. > Well you need to be the database superuser to run the file. > Thanks, > > -- Adrian Klaver adrian.klaver@gmail.com
Yes, I was.
The backup successfully created the schema followed by continuous error messages. It would be nice to have informative error message.
Thanks,
On Sat, Apr 16, 2011 at 7:10 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On Saturday, April 16, 2011 4:05:52 pm Edison So wrote:Well you need to be the database superuser to run the file.
> I did that with Postgres 8.1 backup file on 9.0 database. I got continuous
> error messages like "Can not execute the command...". Well, I do not need
> worry about it for I am going to back up 8.1 database using 9.0 pg_dump
> command.
>
> Let's see how it goes.
>
--
Edison
On Saturday, April 16, 2011 4:24:38 pm Edison So wrote: > Yes, I was. > > The backup successfully created the schema followed by continuous error > messages. It would be nice to have informative error message. What exactly where the error messages? Also the degree of verbosity and level of detail can be set in postgresql.conf. > > Thanks, > -- Adrian Klaver adrian.klaver@gmail.com
I used the option "-v" with the pg_restore command in the 9.0 database machine. If you want the exact error message, I will have to give it to you on Monday.
Thanks,
On Sat, Apr 16, 2011 at 8:17 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On Saturday, April 16, 2011 4:24:38 pm Edison So wrote:What exactly where the error messages? Also the degree of verbosity and level of
> Yes, I was.
>
> The backup successfully created the schema followed by continuous error
> messages. It would be nice to have informative error message.
detail can be set in postgresql.conf.
--
Edison