9.3 migration issue

[Stephen Davies]

I am in the process of migrating several PostgreSQL databases from a 32-bit
V9.1.4 environment to a 64-bit V9.3 environment.

I have used pg_dump and pg_restore (or postgis_restore.pl) as required by the
combination of version and word size migration and the results have been
(superficially) good.

However, some tables in some databases have lost access privileges.
That is, users who could access tables on the old server are denied access on
the new.
I have fixed this by manually granting access where necessary but wonder
whether the original issue is a bug or something that I have missed in the
migration.

Cheers and thanks,
Stephen
--
=============================================================================
Stephen Davies Consulting P/L Phone: [phone]
Adelaide, South Australia. Mobile:[phone]
Records & Collections Management.

[Vick Khera]

Post by Stephen Davies
I have fixed this by manually granting access where necessary but wonder
whether the original issue is a bug or something that I have missed in the
migration.

pg_dump emits the necessary GRANTs for the tables.

Did you use pg_dumpall --globals-only to copy over your users and
their settings?

[Stephen Davies]

No. Just pg_dump and pg_restore/postgis_restore.pl.

Post by Vick Khera

Post by Stephen Davies
I have fixed this by manually granting access where necessary but wonder
whether the original issue is a bug or something that I have missed in the
migration.

pg_dump emits the necessary GRANTs for the tables.
Did you use pg_dumpall --globals-only to copy over your users and
their settings?

--
=============================================================================
Stephen Davies Consulting P/L Phone: [phone]
Adelaide, South Australia. Mobile:[phone]
Records & Collections Management.

[Adrian Klaver]

Post by Stephen Davies
No. Just pg_dump and pg_restore/postgis_restore.pl.

Roles(users) are global to a cluster so they will not be picked up by
pg_dump. You have the options of:

1) Using pg_dumpall to dump the entire cluster into a text file

http://www.postgresql.org/docs/9.3/interactive/app-pg-dumpall.html

$ pg_dumpall > db.out

2) Or do pg_dump on the individual databases and pg_dumpall -g to get
just the global objects, which is what Vick Khera was getting at.

-g
--globals-only

Dump only global objects (roles and tablespaces), no databases.

Post by Stephen Davies

Post by Vick Khera

Post by Stephen Davies
I have fixed this by manually granting access where necessary but wonder
whether the original issue is a bug or something that I have missed in the
migration.

pg_dump emits the necessary GRANTs for the tables.
Did you use pg_dumpall --globals-only to copy over your users and
their settings?

[Stephen Davies]

Thanks for that. I shall use it when I do the repeat migration.

Cheers,
Stephen

Post by Stephen Davies
No. Just pg_dump and pg_restore/postgis_restore.pl.

Roles(users) are global to a cluster so they will not be picked up by pg_dump.
1) Using pg_dumpall to dump the entire cluster into a text file
http://www.postgresql.org/docs/9.3/interactive/app-pg-dumpall.html
$ pg_dumpall > db.out
2) Or do pg_dump on the individual databases and pg_dumpall -g to get just the
global objects, which is what Vick Khera was getting at.
-g
--globals-only
Dump only global objects (roles and tablespaces), no databases.

Post by Stephen Davies

Post by Vick Khera

Post by Stephen Davies
I have fixed this by manually granting access where necessary but wonder
whether the original issue is a bug or something that I have missed in the
migration.

pg_dump emits the necessary GRANTs for the tables.
Did you use pg_dumpall --globals-only to copy over your users and
their settings?

--
=============================================================================
Stephen Davies Consulting P/L Phone: [phone]
Adelaide, South Australia. Mobile:[phone]
Records & Collections Management.

[Jeff Janes]

Post by Stephen Davies
I am in the process of migrating several PostgreSQL databases from a
32-bit V9.1.4 environment to a 64-bit V9.3 environment.
I have used pg_dump and pg_restore (or postgis_restore.pl) as required by
the combination of version and word size migration and the results have
been (superficially) good.
However, some tables in some databases have lost access privileges.
That is, users who could access tables on the old server are denied access
on the new.
I have fixed this by manually granting access where necessary but wonder
whether the original issue is a bug or something that I have missed in the
migration.

Did you get any error messages during the load?

Cheers,

Jeff

[Stephen Davies]

Nope. All went very smoothly apart from these grant issues.

Post by Stephen Davies
I am in the process of migrating several PostgreSQL databases from a
32-bit V9.1.4 environment to a 64-bit V9.3 environment.
I have used pg_dump and pg_restore (or postgis_restore.pl
<http://postgis_restore.pl>) as required by the combination of version and
word size migration and the results have been (superficially) good.
However, some tables in some databases have lost access privileges.
That is, users who could access tables on the old server are denied access
on the new.
I have fixed this by manually granting access where necessary but wonder
whether the original issue is a bug or something that I have missed in the
migration.
Did you get any error messages during the load?
Cheers,
Jeff

--
=============================================================================
Stephen Davies Consulting P/L Phone: [phone]
Adelaide, South Australia. Mobile:[phone]
Records & Collections Management.

[Adrian Klaver]

Post by Stephen Davies
Nope. All went very smoothly apart from these grant issues.

I think what Jeff was after was any error messages related to the grant
issues. I would expect that if users where granted access to tables and
where now denied, there would be an error on restore when that GRANT was
issued.

Post by Stephen Davies

Post by Stephen Davies
I am in the process of migrating several PostgreSQL databases from a
32-bit V9.1.4 environment to a 64-bit V9.3 environment.
I have used pg_dump and pg_restore (or postgis_restore.pl
<http://postgis_restore.pl>) as required by the combination of version and
word size migration and the results have been (superficially) good.
However, some tables in some databases have lost access privileges.
That is, users who could access tables on the old server are denied access
on the new.
I have fixed this by manually granting access where necessary but wonder
whether the original issue is a bug or something that I have missed in the
migration.
Did you get any error messages during the load?
Cheers,
Jeff

[Stephen Davies]

I no longer have the logs but I do not recall any errors during the restores.

The first I knew of the issue was when scripts started failing because access
was denied to some tables for the nominated user.

Due to other, non-PostgreSQL issues, I am going to have to repeat some of the
migrations next week. I shall watch carefully for any grant errors.

Cheers,
Stephen

Post by Adrian Klaver

Post by Stephen Davies
Nope. All went very smoothly apart from these grant issues.

I think what Jeff was after was any error messages related to the grant
issues. I would expect that if users where granted access to tables and where
now denied, there would be an error on restore when that GRANT was issued.

Post by Stephen Davies

Post by Stephen Davies
I am in the process of migrating several PostgreSQL databases from a
32-bit V9.1.4 environment to a 64-bit V9.3 environment.
I have used pg_dump and pg_restore (or postgis_restore.pl
<http://postgis_restore.pl>) as required by the combination of version and
word size migration and the results have been (superficially) good.
However, some tables in some databases have lost access privileges.
That is, users who could access tables on the old server are denied access
on the new.
I have fixed this by manually granting access where necessary but wonder
whether the original issue is a bug or something that I have missed in the
migration.
Did you get any error messages during the load?
Cheers,
Jeff

--
=============================================================================
Stephen Davies Consulting P/L Phone: [phone]
Adelaide, South Australia. Mobile:[phone]
Records & Collections Management.