pg_upgradecluster transfering only a portion of the data
Hello, I tried upgrading pg db from version 9.6 to 14 by using pg_upgradecluster command. I freshly installed pg 14 -> ran pg_dropcluster 14 main --stop -> and then upgraded using pg_upgradecluster 9.6 main. After a successful prompt finished, I checked the database and the size went from originally 20gb (in 9.6) to 700~ mb (in 14) while the disk space available shrank by about 2gb meaning that there is still the 20gb of data. I tried the entire process twice (since I had created an AWS EC2 snapshot for this) and the result was the same. Is my solution to migrating old pg version to the new one wrong? Before this I tried the same process with around 300mb of data and all of that transferred successfully. If I did not understand the pg_upgradecluster command, what would be the best practice when upgrading pg version with huge amounts of data(could be a terabyte)?
Re: pg_upgradecluster transfering only a portion of the data
I did not use the -k --link argument while upgrading as that I presume does not copy the data Dňa po 27. 2. 2023, 18:10 Adrian Klaver napísal(a): > On 2/27/23 09:05, Dávid Suchan wrote: > > Please use Reply All > Ccing list > > > My bad, > > \l+ lists databases and their respective sizes- I used that and also > > pg_size_pretty(), the result size was the same - before it was 20gb for > > the biggest db, after it was 700mb. > > I counted rows before the upgrade in one of the biggest and most > > important table that I was watching and comparing - before there were > > hundreds of thousands or millions of rows(not sure about the exact > > number, just a lot), after the upgrade only like 15, but the first 15 > > rows matched after the upgrade when I checked with select of that table. > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: pg_upgradecluster transfering only a portion of the data
I used "pg_upgradecluster 9.6 main", all commands are in my first message: I freshly installed pg 14 -> ran pg_dropcluster 14 main --stop -> and then upgraded using pg_upgradecluster 9.6 main. po 27. 2. 2023 o 18:40 Laurenz Albe napísal(a): > On Mon, 2023-02-27 at 18:18 +0100, Dávid Suchan wrote: > > I did not use the -k --link argument while upgrading as that I presume > does not copy the data > > It would be great if you shared the exact command line you used. > > The man page of "pg_upgradecluster" says: > > -m, --method=dump|upgrade|link|clone > Specify the upgrade method. dump uses pg_dump(1) and pg_restore(1), > upgrade uses pg_upgrade(1). The default is dump. > > Yours, > Laurenz Albe >
Re: pg_upgradecluster transfering only a portion of the data
1) i downloaded both versions using apt-get install postgres 2) i will check the tablespace and log files tomorrow, i don't have access to the workstation right now. po 27. 2. 2023 o 18:44 Adrian Klaver napísal(a): > On 2/27/23 09:10, Adrian Klaver wrote: > > On 2/27/23 09:05, Dávid Suchan wrote: > > > > Please use Reply All > > Ccing list > > > >> My bad, > >> \l+ lists databases and their respective sizes- I used that and also > >> pg_size_pretty(), the result size was the same - before it was 20gb > >> for the biggest db, after it was 700mb. > >> I counted rows before the upgrade in one of the biggest and most > >> important table that I was watching and comparing - before there were > >> hundreds of thousands or millions of rows(not sure about the exact > >> number, just a lot), after the upgrade only like 15, but the first 15 > >> rows matched after the upgrade when I checked with select of that table. > > Hmm, I can't see how you got that state without there being some sort of > error messages. > > Just to be clear: > > 1) The 9.6 and 14 instances where installed from the same source? > > 2) Are you using tablespaces other then the default? > > > Scan the Postgres log for the 14 instance at /var/log/postgresql for > error messages. > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
pg_upgrade Only the install user can be defined in the new cluster
Hello, Im trying to upgrage the db version to a newer one with the command: '/usr/lib/postgresql/14/bin/pg_upgrade --old-bindir /usr/lib/postgresql/9.6/bin --new-bindir /usr/lib/postgresql/14/bin --old-datadir /etc/postgresql/9.6/main --new-datadir /var/lib/postgresql/14/data -U postgres' (logged as postgres user in ubuntu), but keep getting "Only the install user can be defined in the new cluster" error. I cant understand why it fails, since I am upgrading as a superuser postgres, and there are no other users in the db. I ran 'SELECT rolname FROM pg_roles WHERE oid = 10' command which should confirm that postgres is the creator of the db and it definetly is. The whole log: Finding the real data directory for the source cluster ok Performing Consistency Checks - Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for system-defined composite types in user tables ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for user-defined encoding conversions ok Checking for user-defined postfix operators ok Checking for incompatible polymorphic functions ok Checking for tables WITH OIDS ok Checking for invalid "sql_identifier" user columns ok Checking for invalid "unknown" user columns ok Creating dump of global objects ok Creating dump of database schemas ok Checking for presence of required libraries ok Checking database user is the install user Only the install user can be defined in the new cluster. Failure, exiting
Re: pg_upgrade Only the install user can be defined in the new cluster
Can i create a brand new cluster and check it that way? Since I had to stop / shut down the 14 cluster in order to proceed with pg_upgrade and I dont know how can i connect to it now ... Od: Dávid Suchan Odoslané: streda 15. marca 2023 14:47 Komu: Daniel Gustafsson Predmet: Re: pg_upgrade Only the install user can be defined in the new cluster Can i create a brand new cluster and check it that way? Since I had to stop / shut down the 14 cluster in order to proceed with pg_upgrade and I dont know how can i connect to it now ... Od: Daniel Gustafsson Odoslané: streda 15. marca 2023 13:27 Komu: Dávid Suchan Kópia: pgsql-gene...@postgresql.org Predmet: Re: pg_upgrade Only the install user can be defined in the new cluster > On 15 Mar 2023, at 10:30, Dávid Suchan wrote: > ..there are no other users in the db. The check in question performs this: SELECT COUNT(*) FROM pg_catalog.pg_roles WHERE rolname !~ '^pg_'; What do you get when running that in the new v14 cluster? -- Daniel Gustafsson
Re: pg_upgrade Only the install user can be defined in the new cluster
It prints out: count --- 1 (1 row) Od: Daniel Gustafsson Odoslané: streda 15. marca 2023 13:27 Komu: Dávid Suchan Kópia: pgsql-gene...@postgresql.org Predmet: Re: pg_upgrade Only the install user can be defined in the new cluster > On 15 Mar 2023, at 10:30, Dávid Suchan wrote: > ..there are no other users in the db. The check in question performs this: SELECT COUNT(*) FROM pg_catalog.pg_roles WHERE rolname !~ '^pg_'; What do you get when running that in the new v14 cluster? -- Daniel Gustafsson
Re: pg_upgrade Only the install user can be defined in the new cluster
So I tried upgrading into this brand new cluster I created(using initdb -D /somedatapathichose). Running the upgrade with --check worked, it returned message that the clusters are identical. Then I stopped the new cluster, and ran the pg_upgrade without --check, which resulted in another: Checking database user is the install user Only the install user can be defined in the new cluster. Failure, exiting I dont know what to check for anymore, the log files dont say anything other than "Only the install user can be defined in the new cluster" when postgres is the install user everywhere. The ' SELECT COUNT(*) FROM pg_catalog.pg_roles WHERE rolname !~ '^pg_'; ' prints count 4 and ' SELECT rolname FROM pg_roles WHERE oid = 10; ' prints rolname postgres. Od: Daniel Gustafsson Odoslané: štvrtok 16. marca 2023 10:28 Komu: Dávid Suchan Kópia: pgsql-gene...@postgresql.org Predmet: Re: pg_upgrade Only the install user can be defined in the new cluster > On 15 Mar 2023, at 16:39, Dávid Suchan wrote: > > It prints out: > count > --- > 1 I have a feeling the cluster you tried to upgrade to doesn't match this one, as the check that failed will fail on values other than 1. Did you create them equally? If you try to upgrade into this cluster, even just with the --check option, does that yield more success? -- Daniel Gustafsson
Re: pg_upgrade Only the install user can be defined in the new cluster
rolname --- anon api heartbeat postgres only the default users.. Od: Tom Lane Odoslané: štvrtok 16. marca 2023 19:24 Komu: Dávid Suchan Kópia: Daniel Gustafsson ; pgsql-gene...@postgresql.org Predmet: Re: pg_upgrade Only the install user can be defined in the new cluster =?Windows-1252?Q?D=E1vid_Suchan?= writes: > The ' SELECT COUNT(*) FROM pg_catalog.pg_roles WHERE rolname !~ '^pg_'; ' > prints count 4 4? That would be the problem all right. What are those, that is what do you get from SELECT rolname FROM pg_catalog.pg_roles WHERE rolname !~ '^pg_'; on the new cluster? regards, tom lane
Re: pg_upgrade Only the install user can be defined in the new cluster
I installed both postgres versions on ubuntu machine with 'apt-get install postgres', which installed both client and server packages. Is that where I made a mistake? Od: David G. Johnston Odoslané: pondelok 20. marca 2023 14:57 Komu: Dávid Suchan Kópia: Tom Lane ; Daniel Gustafsson ; pgsql-gene...@postgresql.org Predmet: Re: pg_upgrade Only the install user can be defined in the new cluster On Monday, March 20, 2023, Dávid Suchan mailto:david.suc...@student.tuke.sk>> wrote: rolname --- anon api heartbeat postgres only the default users.. You have an incorrect concept of default here. Only postgres is installed by community PostgresSQL and thus pg_upgrade is rightfully complaining. David J.
Safest pgupgrade jump distance
Hi, I was wondering what is the safest pg_upgrade version upgrade distance going from 9.6 version. Do I need to go version by version or I can go from 9.6 to 15? We have a very huge database(TBs) with one replication server, so we will first run the pgupgrade on the main server and then rsync to a standby replica. I'm not sure whether it's safe to do it from 9.6 to 15 at once, I have tested the process on 9,6 to 10 yet. Would that be a wise approach to such an upgrade of the db? Also, when upgrading a very big database with replication where none of the data can be allowed to be lost, is the pgupgrade into rsync approach the best one? Thanks.
