New feature: selectivity - new attribute in function

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,

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't be) 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 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: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.

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:

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't be) 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 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:

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

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

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

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

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

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

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

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

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

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

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

--
Edison