Problem for restoure data base Postgre

On 16 Mar 2012, at 24:53, BrunoSteven wrote:

I am trying restoure data base from Postgre running on Windows Server 2003 32bits with Postgre with 9.0.3 for a Linux running Centos 6 with Postgre 9.0.7 , but wasn´t working very well .

I post follow link for paste bin with some message error

http://pastebin.com/94qnc8Hj

I don´t understand very well functions Postgre, but I need help for solve this problem.

You may want to check the preferred spelling of the product, it's definitely not Postgre.

It looks like you're using an old version of pg_restore to attempt the restore. CREATE EXTENSION is new to Postgres 9.x, so your pg_restore is probably from a Postgres 8.x installation.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

Alban,

Fist Was installed postgre 8 on Centos after I remove this version and install postgre 9 . Maybe there are rest of installation of version 8 .

Thank you

Sent from my iPhone

On 16/03/2012, at 05:07, Alban Hertroys <haramrae@gmail.com> wrote:

Alban Hertroys <haramrae@gmail.com> writes:

On 16 Mar 2012, at 24:53, BrunoSteven wrote:

I am trying restoure data base from Postgre running on Windows Server 2003 32bits with Postgre with 9.0.3 for a Linux running Centos 6 with Postgre 9.0.7 , but wasn�t working very well .

I post follow link for paste bin with some message error

http://pastebin.com/94qnc8Hj

I don�t understand very well functions Postgre, but I need help for solve this problem.

You may want to check the preferred spelling of the product, it's definitely not Postgre.

It looks like you're using an old version of pg_restore to attempt the restore. CREATE EXTENSION is new to Postgres 9.x, so your pg_restore is probably from a Postgres 8.x installation.

More to the point, CREATE EXTENSION is new as of 9.1. So that dump did
not come from a 9.0.x server, and you're not going to be able to restore
it into a 9.0.x server.

regards, tom lane

Hello,

When creating a serial, a sequence is created automatically.
CREATE TABLE tablename ( colname SERIAL
);
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Should not  a proper permissions based on the table privileges added to the sequence ?. For example, when a table has INSERT, UPDATE permissions on the table  to a certain user, this should be taken into account. 

Regards  

On 03/16/2012 07:06 AM, salah jubeh wrote:

Hello,

When creating a serial, a sequence is created automatically.

CREATE TABLEtablename (
colname SERIAL
);

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

The OWNED BY is for dependency tracking not privileges:

http://www.postgresql.org/docs/9.1/interactive/sql-altersequence.html
OWNED BY table.column
OWNED BY NONE
The OWNED BY option causes the sequence to be associated with a specific
table column, such that if that column (or its whole table) is dropped,
the sequence will be automatically dropped as well. If specified, this
association replaces any previously specified association for the
sequence. The specified table must have the same owner and be in the
same schema as the sequence. Specifying OWNED BY NONE removes any
existing association, making the sequence "free-standing".

Should not a proper permissions based on the table privileges added to
the sequence ?. For example, when a table has INSERT, UPDATE permissions
on the table to a certain user, this should be taken into account.

Regards

--
Adrian Klaver
adrian.klaver@gmail.com

On 03/16/2012 08:00 AM, salah jubeh wrote:

Hello Adrian,

Sorry, I was not clear.

what I meant is that.
GRANT INSERT, UPDATE ON tablenaem TO USER; -- leads to GRANT ALL ON
TABLE tablename_colname_seq TO USER

CCing the list.
Still not following.
What version of Postgres are you using?

Using 9.0.7 here I get:

test=> CREATE TABLE ser_test(id serial);

public | ser_test | table | aklaver
public | ser_test_id_seq | sequence | aklaver

test=> \dp ser_test
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+----------+-------+-------------------+--------------------------
public | ser_test | table | |
(1 row)

test=> \dp ser_test_id_seq
Access privileges
Schema | Name | Type | Access privileges | Column
access privileges
--------+-----------------+----------+-------------------+--------------------------
public | ser_test_id_seq | sequence |

GRANT INSERT, UPDATE ON table ser_test to sales;
GRANT

test=> \dp ser_test
Access privileges
Schema | Name | Type | Access privileges | Column access
privileges
--------+----------+-------+-------------------------+--------------------------
public | ser_test | table | aklaver=arwdDxt/aklaver+|
| | | sales=aw/aklaver |

test=> \dp ser_test_id_seq
Access privileges
Schema | Name | Type | Access privileges | Column
access privileges
--------+-----------------+----------+-------------------+--------------------------
public | ser_test_id_seq | sequence | |

Regards

--
Adrian Klaver
adrian.klaver@gmail.com

On 03/16/2012 08:00 AM, salah jubeh wrote:

Hello Adrian,

Sorry, I was not clear.

what I meant is that.
GRANT INSERT, UPDATE ON tablenaem TO USER; -- leads to GRANT ALL ON
TABLE tablename_colname_seq TO USER

Another thought you do not happen to have DEFAULT PRIVILEGES set up for
sequences:

http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html

In psql \ddp will list them.

Regards

--
Adrian Klaver
adrian.klaver@gmail.com

On Fri, Mar 16, 2012 at 5:21 AM, <aspenbr@gmail.com> wrote:

Alban,

Fist Was installed postgre 8 on Centos after I remove this version and install postgre 9 . Maybe there are rest of installation of version 8 .

That doesn't explain how you wound up with a dump created by 9.1
though. (assuming you installed 9.0 and not 9.1)