Обсуждение: 9.3 migration issue
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: 08-8177 1595 Adelaide, South Australia. Mobile:040 304 0583 Records & Collections Management.
On Mon, Oct 13, 2014 at 12:11 AM, Stephen Davies <sdavies@sdc.com.au> wrote: > 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?
On Sun, Oct 12, 2014 at 9:11 PM, Stephen Davies <sdavies@sdc.com.au> wrote:
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
Nope. All went very smoothly apart from these grant issues. On 14/10/14 01:57, Jeff Janes wrote: > On Sun, Oct 12, 2014 at 9:11 PM, Stephen Davies <sdavies@sdc.com.au > <mailto:sdavies@sdc.com.au>> wrote: > > 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: 08-8177 1595 Adelaide, South Australia. Mobile:040 304 0583 Records & Collections Management.
No. Just pg_dump and pg_restore/postgis_restore.pl. On 13/10/14 22:24, Vick Khera wrote: > On Mon, Oct 13, 2014 at 12:11 AM, Stephen Davies <sdavies@sdc.com.au> wrote: >> 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: 08-8177 1595 Adelaide, South Australia. Mobile:040 304 0583 Records & Collections Management.
On 10/13/2014 04:27 PM, Stephen Davies wrote: > 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. > > On 14/10/14 01:57, Jeff Janes wrote: >> On Sun, Oct 12, 2014 at 9:11 PM, Stephen Davies <sdavies@sdc.com.au >> <mailto:sdavies@sdc.com.au>> wrote: >> >> 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 > > -- Adrian Klaver adrian.klaver@aklaver.com
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 On 14/10/14 10:05, Adrian Klaver wrote: > On 10/13/2014 04:27 PM, Stephen Davies wrote: >> 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. > >> >> On 14/10/14 01:57, Jeff Janes wrote: >>> On Sun, Oct 12, 2014 at 9:11 PM, Stephen Davies <sdavies@sdc.com.au >>> <mailto:sdavies@sdc.com.au>> wrote: >>> >>> 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: 08-8177 1595 Adelaide, South Australia. Mobile:040 304 0583 Records & Collections Management.
On 10/13/2014 04:28 PM, Stephen Davies wrote: > 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. > > On 13/10/14 22:24, Vick Khera wrote: >> On Mon, Oct 13, 2014 at 12:11 AM, Stephen Davies <sdavies@sdc.com.au> >> wrote: >>> 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? >> >> > > -- Adrian Klaver adrian.klaver@aklaver.com
Thanks for that. I shall use it when I do the repeat migration. Cheers, Stephen On 14/10/14 10:21, Adrian Klaver wrote: > On 10/13/2014 04:28 PM, Stephen Davies wrote: >> 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. > > >> >> On 13/10/14 22:24, Vick Khera wrote: >>> On Mon, Oct 13, 2014 at 12:11 AM, Stephen Davies <sdavies@sdc.com.au> >>> wrote: >>>> 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: 08-8177 1595 Adelaide, South Australia. Mobile:040 304 0583 Records & Collections Management.