Need Help On Upgrade
Hello, I upgraded from postgresql 9.5 to 14.8. After the upgrade users were not able to connect due to password hash. The password encryption was on scram-sha-256. The password hash was on md5. in the pg_hba.conf file the authentication method was md5. I do not know why users could not connect because as per postgresql documentation, that should work. I have previously done a similar upgrade and did not face similar issues. Kind regards Johnathan Tiamoh
Re: Need Help On Upgrade
Ok Adrian, Postgres 9.5 postgresql.conf password_encryption = ? md5 > wish is the default for 9.5 pg_hba.conf-> md5 Provide the relevant lines Postgres 14.8 postgresql.conf password_encryption = ? scram-sha-256 > wish is the default for 14.8 pg_hba.conf md5 Thank you. On Mon, Jul 10, 2023 at 3:38 PM Adrian Klaver wrote: > On 7/10/23 11:15, Johnathan Tiamoh wrote: > > Hello, > > > > I upgraded from postgresql 9.5 to 14.8. After the upgrade users were not > > able to connect due to password hash. > > > > The password encryption was on scram-sha-256. The password hash was on > > md5. in the pg_hba.conf file the authentication method was md5. I do not > > know why users could not connect because as per postgresql > > documentation, that should work. > > > > 1) You are going to have to be more specific about where and what the > settings are or where for both Postgres versions and the *.conf files. So: > > Postgres 9.5 > > postgresql.conf > >password_encryption = ? > > pg_hba.conf > > Provide the relevant lines > > Postgres 14.8 > > postgresql.conf > >password_encryption = ? > > pg_hba.conf > > Provide the relevant lines > > 2) Provide the error messages received when trying to connect. > > > > I have previously done a similar upgrade and did not face similar issues. > > > > > > Kind regards > > Johnathan Tiamoh > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Need Help On Upgrade
> Ok Adrian, > > Postgres 9.5 > > postgresql.conf > > password_encryption = ? md5 > wish is the default for 9.5 > > pg_hba.conf-> md5 > > Provide the relevant lines > > Postgres 14.8 > > postgresql.conf > > password_encryption = ? scram-sha-256 > which is the > default for 14.8 > > pg_hba.conf md5 You have not answered: 2) Provide the error messages received when trying to connect. [XXX| APPNAME=[unknown]| DB=proftpd| USER=proftpd| PID=9053| 2| authentication| PGE-28P01: DETAIL: Connection matched pg_hba.conf line 113: "hostall all 0.0.0.0/0 md5" On Mon, Jul 10, 2023 at 4:00 PM Adrian Klaver wrote: > On 7/10/23 12:55, Johnathan Tiamoh wrote: > > Please reply to list also. > Ccing list > > > Ok Adrian, > > > > Postgres 9.5 > > > > postgresql.conf > > > > password_encryption = ? md5 > wish is the default for 9.5 > > > > pg_hba.conf-> md5 > > > > Provide the relevant lines > > > > Postgres 14.8 > > > > postgresql.conf > > > > password_encryption = ? scram-sha-256 > wish is the > > default for 14.8 > > > > pg_hba.conf md5 > > You have not answered: > > 2) Provide the error messages received when trying to connect. > > > > > > > Thank you. > > > > > > On Mon, Jul 10, 2023 at 3:38 PM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 7/10/23 11:15, Johnathan Tiamoh wrote: > > > Hello, > > > > > > I upgraded from postgresql 9.5 to 14.8. After the upgrade users > > were not > > > able to connect due to password hash. > > > > > > The password encryption was on scram-sha-256. The password hash > > was on > > > md5. in the pg_hba.conf file the authentication method was md5. I > > do not > > > know why users could not connect because as per postgresql > > > documentation, that should work. > > > > > > > 1) You are going to have to be more specific about where and what the > > settings are or where for both Postgres versions and the *.conf > > files. So: > > > > Postgres 9.5 > > > > postgresql.conf > > > > password_encryption = ? > > > > pg_hba.conf > > > > Provide the relevant lines > > > > Postgres 14.8 > > > > postgresql.conf > > > > password_encryption = ? > > > > pg_hba.conf > > > > Provide the relevant lines > > > > 2) Provide the error messages received when trying to connect. > > > > > > > I have previously done a similar upgrade and did not face similar > > issues. > > > > > > > > > Kind regards > > > Johnathan Tiamoh > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Need Help On Upgrade
Sorry Adrian, Below is the full error message. 2023-06-27 05:01:27.385 CDT| XXX (60930)| APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| authentication| PGE-28P01: FATAL: password authentication failed for user "grafana" 2023-06-27 05:01:27.385 CDT| XXX (60930)| APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2| authentication| PGE-28P01: DETAIL: Connection matched pg_hba.conf line 113: "host all all 0.0.0.0/0 md5" On Mon, Jul 10, 2023 at 6:36 PM Adrian Klaver wrote: > On 7/10/23 13:20, Johnathan Tiamoh wrote: > > > >> Ok Adrian, > >> > >> Postgres 9.5 > >> > >> postgresql.conf > >> > >> password_encryption = ? md5 > wish is the default for 9.5 > >> > >> pg_hba.conf-> md5 > >> > >> Provide the relevant lines > >> > >> Postgres 14.8 > >> > >> postgresql.conf > >> > >> password_encryption = ? scram-sha-256 > which is the > >> default for 14.8 > >> > >> pg_hba.conf md5 > > > > You have not answered: > > > > 2) Provide the error messages received when trying to connect. > > [XXX| APPNAME=[unknown]| DB=proftpd| USER=proftpd| > > PID=9053| 2| authentication| PGE-28P01: DETAIL: Connection matched > > pg_hba.conf line 113: "hostall all 0.0.0.0/0 > > <http://0.0.0.0/0> md5" > > > > The above is not showing any actual error. There should a part that says > what failed when connecting. > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Need Help On Upgrade
Hello KK, In the beginning, it was a little challenging, but as the process went on it became easy. No, it was not a multistage upgrade. It was possible to upgrade straight from 9.5 to 14. We did not change anything on the schema. For data type, we alter all tables that were created with OIDs to remove the OIDs. We remove all data with abs time ( absolute time). We equally remove all data with sql_identifier. We also created a script to check for unknown data type and another to remove them. For documentation, we did not use any specific documentation. We just followed postgresql documentation on upgrading. We created our one plan and steps based on our clusters, ensuring we can safely roll back in case of any challenges. Thank you. Johnathan T On Tue, Jul 11, 2023 at 5:17 AM KK CHN wrote: > Hi Johnathan, > > 1. How complex was it upgrading from an old Postgres 9.5 to 14.8 ? Is it > multistage upgrading or single shot to 14.8 ? > > 2. What about the old schema ( data types of 9.8 when the database was > designed couple of years ago? and the datatypes supported in 14.8 is > there any manual work was involved in this migration , please let me know > the manual works needed to perform while migration from 9.8 to 14.8) > > 3. Which documentation needs to be followed to perform this upgrade ?? > > Any hints much appreciated.. > Krishane > > On Mon, Jul 10, 2023 at 11:46 PM Johnathan Tiamoh < > johnathantia...@gmail.com> wrote: > >> Hello, >> >> I upgraded from postgresql 9.5 to 14.8. After the upgrade users were not >> able to connect due to password hash. >> >> The password encryption was on scram-sha-256. The password hash was on >> md5. in the pg_hba.conf file the authentication method was md5. I do not >> know why users could not connect because as per postgresql documentation, >> that should work. >> >> I have previously done a similar upgrade and did not face similar issues. >> >> >> Kind regards >> Johnathan Tiamoh >> >
Re: Need Help On Upgrade
I can connect. All applications and other users that connect from to the databases through the pgbouncers can't connect. On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver wrote: > On 7/10/23 20:45, Laurenz Albe wrote: > > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote: > >> Below is the full error message. > >> > >> 2023-06-27 05:01:27.385 CDT| XXX (60930)| > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| authentication| > PGE-28P01: FATAL: password authentication failed for user > >> "grafana" > >> 2023-06-27 05:01:27.385 CDT| XXX (60930)| > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2| authentication| > PGE-28P01: DETAIL: Connection matched pg_hba.conf line 113: > >> "hostall all 0.0.0.0/0 md5" > > > > Then you must have entered the wrong password. > > > > If in doubt, change the password. > > Can you connect to the database at all or is this specific to certain > users? > > What client(s) are you using and is the problem coming from a specific > client? > > > > > > > > Yours, > > Laurenz Albe > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Need Help On Upgrade
Hello Adrian 1) PgBouncer version. pgbouncer.1.7.2 2) Did you change the settings when going from 9.5 to 14.8? No. I did not do any configuration changes on the bouncers 3) Does the PgBouncer log show anything relevant? No. It does not show anything 4) Does '...through the pgbouncers...' mean there is more then one PgBouncer instance in use? Yes, I have more than 3 pgbouncers for different connections. On Tue, Jul 11, 2023 at 12:19 PM Adrian Klaver wrote: > On 7/11/23 08:53, Johnathan Tiamoh wrote: > > I can connect. > > > > All applications and other users that connect from to the databases > > through the pgbouncers can't connect. > > That would have been a good thing to have mentioned in your first post. > I don't use PgBouncer so I am not going to be of much use going forward. > For those that do use it and can help answers to the following would be > helpful: > > 1) PgBouncer version. > > 2) Did you change the settings when going from 9.5 to 14.8? > > 3) Does the PgBouncer log show anything relevant? > > 4) Does '...through the pgbouncers...' mean there is more then one > PgBouncer instance in use? > > > > > On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver > > mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 7/10/23 20:45, Laurenz Albe wrote: > > > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote: > > >> Below is the full error message. > > >> > > >> 2023-06-27 05:01:27.385 CDT| XXX (60930)| > > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| > > authentication| PGE-28P01: FATAL: password authentication failed > > for user > > >> "grafana" > > >> 2023-06-27 05:01:27.385 CDT| XXX (60930)| > > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2| > > authentication| PGE-28P01: DETAIL: Connection matched pg_hba.conf > > line 113: > > >> "hostall all 0.0.0.0/0 > > <http://0.0.0.0/0> md5" > > > > > > Then you must have entered the wrong password. > > > > > > If in doubt, change the password. > > > > Can you connect to the database at all or is this specific to > > certain users? > > > > What client(s) are you using and is the problem coming from a > specific > > client? > > > > > > > > > > > > > > Yours, > > > Laurenz Albe > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Need Help On Upgrade
Thank you very much @Adrian Klaver . On Tue, Jul 11, 2023 at 7:32 PM Adrian Klaver wrote: > On 7/11/23 14:45, Johnathan Tiamoh wrote: > > Hello Adrian > > > > 1) PgBouncer version. > > > > pgbouncer.1.7.2 > > PgBouncer most recent version is from here: > > http://www.pgbouncer.org/changelog.html > > PgBouncer 1.19.x > > 2023-05-31 - PgBouncer 1.19.1 - “Sunny Spring” > > > > > And since 1.7.x there have been these related auth changes: > > PgBouncer 1.11.x > > Add support for SCRAM authentication for clients and servers. A new > authentication type scram-sha-256 is added. > > PgBouncer 1.12.x > > Accept SCRAM channel binding enabled clients. Previously, a client > supporting channel binding (that is, PostgreSQL 11+) would get a > connection failure when connecting to PgBouncer in certain situations. > (PgBouncer does not support channel binding. This change just fixes > support for clients that offer it.) > > PgBouncer 1.14.x > > Add SCRAM authentication pass-through. This allows using encrypted SCRAM > secrets in PgBouncer (either in userlist.txt or from auth_query) for > logging into servers > > PgBouncer 1.16.x > > Mixed use of md5 and scram via hba has been fixed. > > PgBouncer 1.17.x > > Don’t apply fast-fail at connect time. This is part of the > above-mentioned change to not report server errors before > authentication. It also fixes a particular situation with SCRAM > pass-through authentication, where we need to allow the client-side > authentication exchange in order to be able to fix the server-side > connection by re-authenticating. The fast-fail mechanism still applies > right after authentication, so the effective observed behavior will be > the same in most situations. > > > I think an update is in order. > > > > > 2) Did you change the settings when going from 9.5 to 14.8? > > > > No. I did not do any configuration changes on the bouncers > > > > 3) Does the PgBouncer log show anything relevant? > > > > No. It does not show anything > > > > 4) Does '...through the pgbouncers...' mean there is more then one > > PgBouncer instance in use? > > > > Yes, I have more than 3 pgbouncers for different connections. > > > > > > > > On Tue, Jul 11, 2023 at 12:19 PM Adrian Klaver > > mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 7/11/23 08:53, Johnathan Tiamoh wrote: > > > I can connect. > > > > > > All applications and other users that connect from to the > databases > > > through the pgbouncers can't connect. > > > > That would have been a good thing to have mentioned in your first > post. > > I don't use PgBouncer so I am not going to be of much use going > > forward. > > For those that do use it and can help answers to the following would > be > > helpful: > > > > 1) PgBouncer version. > > > > 2) Did you change the settings when going from 9.5 to 14.8? > > > > 3) Does the PgBouncer log show anything relevant? > > > > 4) Does '...through the pgbouncers...' mean there is more then one > > PgBouncer instance in use? > > > > > > > > On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver > > > mailto:adrian.kla...@aklaver.com> > > <mailto:adrian.kla...@aklaver.com > > <mailto:adrian.kla...@aklaver.com>>> wrote: > > > > > > On 7/10/23 20:45, Laurenz Albe wrote: > > > > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote: > > > >> Below is the full error message. > > > >> > > > >> 2023-06-27 05:01:27.385 CDT| XXX > (60930)| > > > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| > > > authentication| PGE-28P01: FATAL: password authentication > failed > > > for user > > > >> "grafana" > > > >> 2023-06-27 05:01:27.385 CDT| XXX > (60930)| > > > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2| > > > authentication| PGE-28P01: DETAIL: Connection matched > > pg_hba.conf > > > line 113: > > > >> "hostall all 0.0.0.0/0 <http://0.0.0.0/0> > > > <http://0.0.0.0/0 <http://0.0.0.0/0>> md5" > > > > >
Reset Postgresql users password
Hello, I wish to find out if there is a way to reset all users in Postgresql password to the same password at once. Thank you Tiamoh J
Re: Reset Postgresql users password
Thank you all very much.
I really appreciate !!
On Wed, Jul 12, 2023 at 4:17 PM Gurjeet Singh wrote:
> On Wed, Jul 12, 2023 at 12:42 PM Mateusz Henicz
> wrote:
> >
> > You can also just write an sql and execute it, like:
> >
> > select 'alter user '|| usename ||' with password ''newpassword'';' from
> pg_user;
> > \gexec
>
> Note that the above assumes you're using psql.
>
> For tools other than psql, you'd have to use dynamic SQL something
> like the DO block below. It will work on psql, just as well.
>
> DO $$
> declare
> rec record;
> begin
> for rec in select
> 'alter user '|| quote_ident(usename)
> ||' with password '|| quote_literal('newpassword')
> as c from pg_user
> loop
> raise notice 'Executing command: %', rec.c;
> execute rec.c;
> end loop;
> end;
> $$;
>
> Best regards,
> Gurjeet
> http://Gurje.et
>
>
>
Upgrade Failure
Hello, I was running an upgrade from 9.5 to 14.8 and encountered the below error. Running in verbose mode Performing Consistency Checks - Checking cluster versions ok The source cluster was not shut down cleanly. Failure, exiting Please how do you resolve this Kind Regards Tiamoh M
Re: Upgrade Failure
Thank You Laurenz. On Tue, Jul 18, 2023 at 6:12 AM Laurenz Albe wrote: > On Tue, 2023-07-18 at 06:02 -0400, Johnathan Tiamoh wrote: > > I was running an upgrade from 9.5 to 14.8 and encountered the below > error. > > > > Running in verbose mode > > Performing Consistency Checks > > - > > Checking cluster versions ok > > > > The source cluster was not shut down cleanly. > > Failure, exiting > > > > Please how do you resolve this > > Well, start the 9.5 server and shut it down cleanly. > > Yours, > Laurenz Albe >
Re: Upgrade Failure
I used pg_ctl to stop the cluster. When I used the same pg_ctl to check status, it says no server is running On Tue, Jul 18, 2023 at 6:14 AM Daniel Gustafsson wrote: > > On 18 Jul 2023, at 12:02, Johnathan Tiamoh > wrote: > > > The source cluster was not shut down cleanly. > > Failure, exiting > > > > Please how do you resolve this > > pg_upgrade runs bin/pg_controldata on the source cluster data directory and > ensures that the "Database cluster state" says "shut down". If you run the > same command, which state is the cluster reported to be in? > > -- > Daniel Gustafsson > >
PGE-XX000: ERROR: Problem with the SSL CA cert (path? access rights?)
Hello How can this error be resolved? PGE-XX000: ERROR: Problem with the SSL CA cert (path? access rights?) Is there any postgresql documentation on this ? Kind regards Johnathan T.
what do you do after pg_walreset ?
Hello, Please I wish to find out what you do after running pg_walreset. I made a mistake and deleted my data directory with all its content. I then restore with 1 day old backups, after that i could start unless i did pg_walreset. 1, I wish to find out what to do after i have started postgresql ? 2, How do I apply the wall files after the reset ? 3, How can I determine the amount of data lost ?
Re: what do you do after pg_walreset ?
Hello Laurenz, Thank you very much for your reply. On Fri, Dec 22, 2023 at 7:10 AM Laurenz Albe wrote: > On Thu, 2023-12-21 at 18:53 -0500, Johnathan Tiamoh wrote: > > Please I wish to find out what you do after running pg_walreset. > > You mean pg_resetwal. > > > I made a mistake and deleted my data directory with all its content. > > I then restore with 1 day old backups, after that i could start > > unless i did pg_walreset. > > Which means that the backup was broken. > > > 1, I wish to find out what to do after i have started postgresql ? > > pg_dumpall the cluster and restore the result to a new cluster > to get rod of the data corruption introduced by pg_resetwal. > > > 2, How do I apply the wall files after the reset ? > > You cannot do that. > > > 3, How can I determine the amount of data lost ? > > Well, everything from after the "backup". > > Yours, > Laurenz Albe >
Unable to start postgresql-14
Hello, I am able to start postgresql because of the below error. Please is there any help [image: User] You FATAL: xlog flush request 4392/164662F8 is not satisfied --- flushed only to 4392/F58 [image: User]YouFATAL: xlog flush request 4392/164662F8 is not satisfied --- flushed only to 4392/F58
Re: Unable to start postgresql-14
More information needed: 1) Exact Postgres version e.g 14.x Postgresql Version 14. 10 2) Is it community version or fork? Community version 3) What was the command you used to start the cluster? systemctl start postgresql-14 4) Do you have replication/WAL archiving set up? Yes. Just to add. The data directory was accidentally deleted. At the time of deletion. The secondary was broken. I restore from backup and it couldn't start. I ended up doing a pg_resetwal to start it and ended up with lots of data corruption. I have try a pg_dumpall to restore in a new server and it has equally failed. On Sat, Dec 23, 2023 at 11:35 AM Adrian Klaver wrote: > On 12/22/23 22:48, Johnathan Tiamoh wrote: > > Hello, > > > > I am able to start postgresql because of the below error. Please is > > there any help > > > > User > > You > > FATAL: xlog flush request 4392/164662F8 is not satisfied --- flushed > > only to 4392/F58 > > > > > > UserYouFATAL: xlog flush request 4392/164662F8 is not satisfied --- > > flushed only to 4392/F58 > > > > More information needed: > > 1) Exact Postgres version e.g 14.x > > 2) Is it community version or fork? > > 3) What was the command you used to start the cluster? > > 4) Do you have replication/WAL archiving set up? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Unable to start postgresql-14
Was this backup taken before the data directory was deleted? Yes. I restored it from a 1-day old backup, but unfortunately, I couldn't apply the logs. If so how was the backup done? It was taken with a customized script that uses pg_dump. Does this mean you recreated the data directory from some source and then ran pg_resetwal? I recreated it from the backup Do you mean you did a pg_dumpall from the Postgres instance running on the restored data directory? Yes. And it failed On Sat, Dec 23, 2023 at 5:35 PM Adrian Klaver wrote: > On 12/23/23 10:05, Johnathan Tiamoh wrote: > > More information needed: > > > > 1) Exact Postgres version e.g 14.x > > > > Postgresql Version 14. 10 > > > > 2) Is it community version or fork? > > > > Community version > > > > 3) What was the command you used to start the cluster? > > > > systemctl start postgresql-14 > > > > 4) Do you have replication/WAL archiving set up? > > > > Yes. > > > > > > Just to add. The data directory was accidentally deleted. At the time of > > deletion. The secondary was broken. > > So that is the cause of the issue. > > > I restore from backup and it couldn't start. > > Was this backup taken before the data directory was deleted? > > If so how was the backup done? > > > > > I ended up doing a pg_resetwal to start it and ended up with lots of > > data corruption. > > Does this mean you recreated the data directory from some source and > then ran pg_resetwal? > > > > > I have try a pg_dumpall to restore in a new server and it has equally > > failed. > > Do you mean you did a pg_dumpall from the Postgres instance running on > the restored data directory? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
ERROR: missing chunk number 0 for toast value 1563962066 in pg_toast_2619
Hello, Please, I need help to fix this. Can't do anything in the database. This error keeps popping up. I have tried to do a pg_dump and the same error keeps coming up. Thank you Johnathan Tiamoh
pg_basebackup Restore problem
Hello, I am trying to restore a pg_basebackup and have the following errors. nohup: ignoring input tar: /tnt/backup/current/7400.tar.gz: Not found in archive tar: /tnt/backup/current/7401.tar.gz: Not found in archive tar: /tnt/backup/current/7402.tar.gz: Not found in archive tar: /tnt/backup/current/base.tar.gz: Not found in archive tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive tar: Exiting with failure status due to previous errors PG_14_202107181/ tar: PG_14_202107181: Cannot mkdir: Permission denied PG_14_202107181/137502/ tar: PG_14_202107181: Cannot mkdir: Permission denied tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory PG_14_202107181/137502/3222926016 tar: PG_14_202107181: Cannot mkdir: Permission denied The files exist in the specified directory. Thank you Johnathan T.
Re: pg_basebackup Restore problem
You need to tell us the PG version number *and* show us the full command
you ran. ?
Postgresql Version 14.10
tar -h -zxvf $PATH_FOLDER/* .tar.gz
ls $PATH_FOLDER/*.tar.gz | xargs -I {} tar -h -zxvf {}
What user are you running pg_basebackup as?
I ran it as postgres and now I'm restoring as postgres
/tnt??? Or /mnt?
/tnt/backup/current
This is where the backup files are.
On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson
wrote:
> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> Hello,
>>
>> I am trying to restore a pg_basebackup and have the following errors.
>>
>>
> You need to tell us the PG version number *and* show us the full command
> you ran.
>
>
>> nohup: ignoring input
>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>
> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>> tar: Exiting with failure status due to previous errors
>> PG_14_202107181/
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>
>
> What user are you running pg_basebackup as?
>
>
>> PG_14_202107181/137502/
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
>> PG_14_202107181/137502/3222926016
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>
>> The files exist in the specified directory.
>>
>
> /tnt??? Or /mnt?
>
>
Re: pg_basebackup Restore problem
1. What's in $PATH_FOLDER?
/tnt/backup/current
2. What pg_basebackup command did you use?
pg_basebackup -D "$baseback_dir" --format=tar \
"${comp_opts[@]}" --wal-method=stream --no-password
--verbose "${PG_DUMP_OPTS[@]}"
/bin/mv "$baseback_dir"/* "/enf/backup/current/"
/bin/rm -r "$baseback_dir"
3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson
wrote:
> 1. What's in $PATH_FOLDER?
> 2. What pg_basebackup command did you use?
> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>
> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> You need to tell us the PG version number *and* show us the full command
>> you ran. ?
>>
>> Postgresql Version 14.10
>>
>> tar -h -zxvf $PATH_FOLDER/* .tar.gz
>>
>> ls $PATH_FOLDER/*.tar.gz | xargs -I {} tar -h -zxvf {}
>>
>> What user are you running pg_basebackup as?
>>
>> I ran it as postgres and now I'm restoring as postgres
>>
>> /tnt??? Or /mnt?
>> /tnt/backup/current
>>
>> This is where the backup files are.
>>
>>
>>
>>
>>
>> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson
>> wrote:
>>
>>> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
>>> johnathantia...@gmail.com> wrote:
>>>
>>>> Hello,
>>>>
>>>> I am trying to restore a pg_basebackup and have the following errors.
>>>>
>>>>
>>> You need to tell us the PG version number *and* show us the full
>>> command you ran.
>>>
>>>
>>>> nohup: ignoring input
>>>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>>>
>>> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>>>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>>>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>>>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>>>> tar: Exiting with failure status due to previous errors
>>>> PG_14_202107181/
>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>
>>>
>>> What user are you running pg_basebackup as?
>>>
>>>
>>>> PG_14_202107181/137502/
>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
>>>> PG_14_202107181/137502/3222926016
>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>
>>>> The files exist in the specified directory.
>>>>
>>>
>>> /tnt??? Or /mnt?
>>>
>>>
Re: pg_basebackup Restore problem
Yes.
I am trying to restore the backups on a standby
On Wed, Jan 17, 2024 at 4:04 PM Ron Johnson wrote:
> Wait a minute... *tar* is throwing the errors, not pg_basebackup, no?
>
> On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> 1. What's in $PATH_FOLDER?
>>
>> /tnt/backup/current
>>
>> 2. What pg_basebackup command did you use?
>>
>> pg_basebackup -D "$baseback_dir" --format=tar \
>> "${comp_opts[@]}" --wal-method=stream --no-password
>> --verbose "${PG_DUMP_OPTS[@]}"
>>
>> /bin/mv "$baseback_dir"/* "/enf/backup/current/"
>> /bin/rm -r "$baseback_dir"
>>
>>
>> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>>
>>
>> On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson
>> wrote:
>>
>>> 1. What's in $PATH_FOLDER?
>>> 2. What pg_basebackup command did you use?
>>> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>>>
>>> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
>>> johnathantia...@gmail.com> wrote:
>>>
>>>> You need to tell us the PG version number *and* show us the full
>>>> command you ran. ?
>>>>
>>>> Postgresql Version 14.10
>>>>
>>>> tar -h -zxvf $PATH_FOLDER/* .tar.gz
>>>>
>>>> ls $PATH_FOLDER/*.tar.gz | xargs -I {} tar -h -zxvf {}
>>>>
>>>> What user are you running pg_basebackup as?
>>>>
>>>> I ran it as postgres and now I'm restoring as postgres
>>>>
>>>> /tnt??? Or /mnt?
>>>> /tnt/backup/current
>>>>
>>>> This is where the backup files are.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson
>>>> wrote:
>>>>
>>>>> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
>>>>> johnathantia...@gmail.com> wrote:
>>>>>
>>>>>> Hello,
>>>>>>
>>>>>> I am trying to restore a pg_basebackup and have the following errors.
>>>>>>
>>>>>>
>>>>> You need to tell us the PG version number *and* show us the full
>>>>> command you ran.
>>>>>
>>>>>
>>>>>> nohup: ignoring input
>>>>>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>>>>>
>>>>> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>>>>>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>>>>>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>>>>>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>>>>>> tar: Exiting with failure status due to previous errors
>>>>>> PG_14_202107181/
>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>>
>>>>>
>>>>> What user are you running pg_basebackup as?
>>>>>
>>>>>
>>>>>> PG_14_202107181/137502/
>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
>>>>>> PG_14_202107181/137502/3222926016
>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>>
>>>>>> The files exist in the specified directory.
>>>>>>
>>>>>
>>>>> /tnt??? Or /mnt?
>>>>>
>>>>>
Re: pg_basebackup Restore problem
Ok.
I'm a little confused because has always work
On Wed, Jan 17, 2024 at 4:11 PM Ron Johnson wrote:
> Then you've got a bug somewhere in:
> tar -h -zxvf $PATH_FOLDER/* .tar.gz
> ls $PATH_FOLDER/*.tar.gz | xargs -I {} tar -h -zxvf {}
>
> On Wed, Jan 17, 2024 at 4:07 PM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> Yes.
>>
>> I am trying to restore the backups on a standby
>>
>> On Wed, Jan 17, 2024 at 4:04 PM Ron Johnson
>> wrote:
>>
>>> Wait a minute... *tar* is throwing the errors, not pg_basebackup, no?
>>>
>>> On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh <
>>> johnathantia...@gmail.com> wrote:
>>>
>>>> 1. What's in $PATH_FOLDER?
>>>>
>>>> /tnt/backup/current
>>>>
>>>> 2. What pg_basebackup command did you use?
>>>>
>>>> pg_basebackup -D "$baseback_dir" --format=tar \
>>>> "${comp_opts[@]}" --wal-method=stream --no-password
>>>> --verbose "${PG_DUMP_OPTS[@]}"
>>>>
>>>> /bin/mv "$baseback_dir"/* "/enf/backup/current/"
>>>> /bin/rm -r "$baseback_dir"
>>>>
>>>>
>>>> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>>>>
>>>>
>>>> On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson
>>>> wrote:
>>>>
>>>>> 1. What's in $PATH_FOLDER?
>>>>> 2. What pg_basebackup command did you use?
>>>>> 3. Why aren't you letting pg_basebackup maintain the WAL files it
>>>>> needs?
>>>>>
>>>>> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
>>>>> johnathantia...@gmail.com> wrote:
>>>>>
>>>>>> You need to tell us the PG version number *and* show us the full
>>>>>> command you ran. ?
>>>>>>
>>>>>> Postgresql Version 14.10
>>>>>>
>>>>>> tar -h -zxvf $PATH_FOLDER/* .tar.gz
>>>>>>
>>>>>> ls $PATH_FOLDER/*.tar.gz | xargs -I {} tar -h -zxvf {}
>>>>>>
>>>>>> What user are you running pg_basebackup as?
>>>>>>
>>>>>> I ran it as postgres and now I'm restoring as postgres
>>>>>>
>>>>>> /tnt??? Or /mnt?
>>>>>> /tnt/backup/current
>>>>>>
>>>>>> This is where the backup files are.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson
>>>>>> wrote:
>>>>>>
>>>>>>> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
>>>>>>> johnathantia...@gmail.com> wrote:
>>>>>>>
>>>>>>>> Hello,
>>>>>>>>
>>>>>>>> I am trying to restore a pg_basebackup and have the following
>>>>>>>> errors.
>>>>>>>>
>>>>>>>>
>>>>>>> You need to tell us the PG version number *and* show us the full
>>>>>>> command you ran.
>>>>>>>
>>>>>>>
>>>>>>>> nohup: ignoring input
>>>>>>>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>>>>>>>
>>>>>>> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>>>>>>>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>>>>>>>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>>>>>>>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>>>>>>>> tar: Exiting with failure status due to previous errors
>>>>>>>> PG_14_202107181/
>>>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>>>>
>>>>>>>
>>>>>>> What user are you running pg_basebackup as?
>>>>>>>
>>>>>>>
>>>>>>>> PG_14_202107181/137502/
>>>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>>>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
>>>>>>>> PG_14_202107181/137502/3222926016
>>>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>>>>
>>>>>>>> The files exist in the specified directory.
>>>>>>>>
>>>>>>>
>>>>>>> /tnt??? Or /mnt?
>>>>>>>
>>>>>>>
Re: pg_basebackup Restore problem
Thank You very much for your time.
On Wed, Jan 17, 2024 at 4:51 PM Ron Johnson wrote:
> Perfectly understandable, but tar *did* fail. Time to start debugging
> your shell script.
>
> On Wed, Jan 17, 2024 at 4:26 PM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> Ok.
>>
>> I'm a little confused because has always work
>>
>> On Wed, Jan 17, 2024 at 4:11 PM Ron Johnson
>> wrote:
>>
>>> Then you've got a bug somewhere in:
>>> tar -h -zxvf $PATH_FOLDER/* .tar.gz
>>> ls $PATH_FOLDER/*.tar.gz | xargs -I {} tar -h -zxvf {}
>>>
>>> On Wed, Jan 17, 2024 at 4:07 PM Johnathan Tiamoh <
>>> johnathantia...@gmail.com> wrote:
>>>
>>>> Yes.
>>>>
>>>> I am trying to restore the backups on a standby
>>>>
>>>> On Wed, Jan 17, 2024 at 4:04 PM Ron Johnson
>>>> wrote:
>>>>
>>>>> Wait a minute... *tar* is throwing the errors, not pg_basebackup, no?
>>>>>
>>>>> On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh <
>>>>> johnathantia...@gmail.com> wrote:
>>>>>
>>>>>> 1. What's in $PATH_FOLDER?
>>>>>>
>>>>>> /tnt/backup/current
>>>>>>
>>>>>> 2. What pg_basebackup command did you use?
>>>>>>
>>>>>> pg_basebackup -D "$baseback_dir" --format=tar \
>>>>>> "${comp_opts[@]}" --wal-method=stream --no-password
>>>>>> --verbose "${PG_DUMP_OPTS[@]}"
>>>>>>
>>>>>> /bin/mv "$baseback_dir"/* "/enf/backup/current/"
>>>>>> /bin/rm -r "$baseback_dir"
>>>>>>
>>>>>>
>>>>>> 3. Why aren't you letting pg_basebackup maintain the WAL files it
>>>>>> needs?
>>>>>>
>>>>>>
>>>>>> On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson
>>>>>> wrote:
>>>>>>
>>>>>>> 1. What's in $PATH_FOLDER?
>>>>>>> 2. What pg_basebackup command did you use?
>>>>>>> 3. Why aren't you letting pg_basebackup maintain the WAL files it
>>>>>>> needs?
>>>>>>>
>>>>>>> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
>>>>>>> johnathantia...@gmail.com> wrote:
>>>>>>>
>>>>>>>> You need to tell us the PG version number *and* show us the full
>>>>>>>> command you ran. ?
>>>>>>>>
>>>>>>>> Postgresql Version 14.10
>>>>>>>>
>>>>>>>> tar -h -zxvf $PATH_FOLDER/* .tar.gz
>>>>>>>>
>>>>>>>> ls $PATH_FOLDER/*.tar.gz | xargs -I {} tar -h -zxvf {}
>>>>>>>>
>>>>>>>> What user are you running pg_basebackup as?
>>>>>>>>
>>>>>>>> I ran it as postgres and now I'm restoring as postgres
>>>>>>>>
>>>>>>>> /tnt??? Or /mnt?
>>>>>>>> /tnt/backup/current
>>>>>>>>
>>>>>>>> This is where the backup files are.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson <
>>>>>>>> ronljohnso...@gmail.com> wrote:
>>>>>>>>
>>>>>>>>> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
>>>>>>>>> johnathantia...@gmail.com> wrote:
>>>>>>>>>
>>>>>>>>>> Hello,
>>>>>>>>>>
>>>>>>>>>> I am trying to restore a pg_basebackup and have the following
>>>>>>>>>> errors.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>> You need to tell us the PG version number *and* show us the full
>>>>>>>>> command you ran.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>> nohup: ignoring input
>>>>>>>>>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>>>>>>>>>
>>>>>>>>> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>>>>>>>>>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>>>>>>>>>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>>>>>>>>>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>>>>>>>>>> tar: Exiting with failure status due to previous errors
>>>>>>>>>> PG_14_202107181/
>>>>>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> What user are you running pg_basebackup as?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>> PG_14_202107181/137502/
>>>>>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>>>>>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or
>>>>>>>>>> directory
>>>>>>>>>> PG_14_202107181/137502/3222926016
>>>>>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>>>>>>
>>>>>>>>>> The files exist in the specified directory.
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> /tnt??? Or /mnt?
>>>>>>>>>
>>>>>>>>>
Postgresql BUG / Help Needed
Hello, I am having the following issue below. Please, I need help to fix it. [image: User] *ERROR: could not access status of transaction 756525298* *Could not open file "pg_xact/02C8": No such file or directory.* Thank You Johnathan T
Re: Postgresql BUG / Help Needed
Thank you Laurenz! Is there a way of preventing this from happening ? On Thu, Feb 1, 2024 at 2:36 AM Laurenz Albe wrote: > On Thu, 2024-02-01 at 00:48 -0500, Johnathan Tiamoh wrote: > > I am having the following issue below. Please, I need help to fix it. > > > > ERROR: could not access status of transaction 756525298 > > Could not open file "pg_xact/02C8": No such file or directory. > > That is data corruption, and you should restore your backup. > > Yours, > Laurenz Albe >
Re: Postgresql BUG / Help Needed
I haven't had difficulties in this cluster recently. However I upgraded it from version 9.5 to 14.10 about 3 weeks ago. On Thu, Feb 1, 2024 at 9:06 AM Jehan-Guillaume de Rorthais wrote: > On Thu, 1 Feb 2024 08:28:45 -0500 > Johnathan Tiamoh wrote: > > > Thank you Laurenz! > > > > Is there a way of preventing this from happening ? > > This could either come from a hardware issue, or easily from your own > actions > or procedures. > > Did you have some difficulties with your instance lately? exceptional > intervention? backup restoration? failover? >
Re: Safest pgupgrade jump distance
I have upgraded from 9.5 to 14 using the -link option. It works fine. I equally had streaming replication running on it. I break(split brain) replication and upgrade the standby, once it done and everything is running smoothly, I then install version 14 to the old primary and just configure streaming on it and all now runs on 14. On Mon, Feb 12, 2024 at 5:08 AM Dávid Suchan wrote: > 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. >
DETAIL: Could not open file "pg_xact/0EEA": No such file or directory
Hello, Please, I need help in resolving this issue. I recently upgraded to postgresql and have experienced the above error with 2 clusters already. I keep losing data in the pg_exact directory. Kind regards Johnathan T
Re: DETAIL: Could not open file "pg_xact/0EEA": No such file or directory
Thank you very much @Adrian Klaver . On Mon, Feb 19, 2024 at 2:45 PM Adrian Klaver wrote: > On 2/19/24 11:36, Johnathan Tiamoh wrote: > > Reply to list also. > Cc'ing list. > > > Sorry Adrian, > > > > This is occurring at every checkpoint. This actually has to do with > > Listen/Notify. > > It is not really linked to the upgrade per say. > > I would say it is. > > From: > > https://www.postgresql.org/docs/current/storage-file-layout.html > > pg_xact Subdirectory containing transaction commit status data > > I'm guessing what ever process you used to upgrade did not transfer all > the files. Or you have some ongoing process that is removing the files > prematurely. > > > > > However, This is occurring in clusters that I recently upgraded to 14. > > Which contradicts, "It is not really linked to the upgrade per say.". > > > > > > > > > > > On Mon, Feb 19, 2024 at 12:17 PM Adrian Klaver > > mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 2/19/24 08:59, Johnathan Tiamoh wrote: > > > Hello, > > > > > > Please, I need help in resolving this issue. I recently upgraded > to > > > postgresql and have experienced the above error with 2 clusters > > already. > > > > > > I keep losing data in the pg_exact directory. > > > > We have been down this rabbit hole multiple times with your upgrading > > procedures. By now you should know that the above does not qualify as > > close to being a sufficient problem report. Respond with a complete > > explanation of your setup and the commands run that got you into this > > situation. > > > > > > > > > > Kind regards > > > Johnathan T > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
User pooler error: unsupported startup parameter: extra_float_digits
Hello, I need help to resolve the above issue. Kind regards Johnathan T.
pooler error : client _idle_timeout
Hello, Please, I need help to fix this issue. I have increase client _idle_timeout from 15 minutes to 30 minutes and still facing the same problem. Kind regards Johnathan T.
Re: User pooler error: unsupported startup parameter: extra_float_digits
Thank you very much ! On Tue, Feb 20, 2024 at 9:50 AM Greg Sabino Mullane wrote: > Add this to your pgbouncer config file: > > ignore_startup_parameters = extra_float_digits > > A web search of your subject line finds this solution quite easily. > Providing more details would also be a good idea for future emails. > > Cheers, > Greg > >
Postgres database encryption
Hello, Good morning. Please I wish to find out the best way of encrypting a Postgres database. This could either be encrypting a whole database ( I don’t if this is possible), a specific table and a specific tablespace. King regards Johnathan T.
Performance degradation after upgrading from 9.5 to 14
Hello, I performed an upgrade from postgresql-9.5 to postgresql-14 and the performance has degraded drastically. Please, is they any advice on getting performance back ? King Regards Johnathan T.
Re: Performance degradation after upgrading from 9.5 to 14
1) How did you upgrade? pg_dump or pg_upgrade? I use pg_ugrade with kink option. 2) Did you run ANALYZE to collect statistics after the upgrade? Yes. I ran vacuumdb-analyze in stages after the upgrade 3) Did you transfer the configuration, or did you just create a new cluster with the default values? I transfer the configuration 4) What exactly is slower? Queries? Inserts? queries 5) Can you quantify the impact? Is it 2x slower? 100x slower? it's more than 5 times slower than before. Very high load averages On Wed, Apr 17, 2024 at 1:25 PM Tomas Vondra wrote: > On 4/17/24 19:13, Johnathan Tiamoh wrote: > > Hello, > > > > > > I performed an upgrade from postgresql-9.5 to postgresql-14 and the > > performance has degraded drastically. > > > > Please, is they any advice on getting performance back ? > > > > There's very little practical advice we can provide based on this > report, because it's missing any useful details. There's a number of > things that might have caused this, but we'd have to speculate. > > For example: > > 1) How did you upgrade? pg_dump or pg_upgrade? > > 2) Did you run ANALYZE to collect statistics after the upgrade? > > 3) Did you transfer the configuration, or did you just create a new > cluster with the default values? > > 4) What exactly is slower? Queries? Inserts? > > 5) Can you quantify the impact? Is it 2x slower? 100x slower? > > > regards > > > -- > Tomas Vondra > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
