Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Jeremy Smith]

>
>
> Is anybody able spotting, what am I doing wrong here? Thank you
>


Unless I'm mistaken, it seems like you are trying to run jetty and postgres
in the same container.  You should really use two containers and run them
both with docker compose.  Docker is very much designed to run a single
service.  This should also greatly simplify your Dockerfile - you could
probably just use the vanilla postgres image for the postgres container.
Here's some info on how to achieve this:

https://www.docker.com/blog/how-to-use-the-postgres-docker-official-image/#Using-Docker-Compose

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Alexander Farber]

Thank you Torsten,

On Mon, Dec 2, 2024 at 12:32 PM Torsten Krah  wrote:

> Am Sonntag, dem 01.12.2024 um 21:59 +0100 schrieb Alexander Farber:
> > But I am yet not sure how to enable listening at localhost again
>
> Instead of using a TCP/IP connection, why not use the unix socket to
> connect to your database [1]?
>
> [1] https://jdbc.postgresql.org/documentation/use/#unix-sockets
>
>
I will try that option too!

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Alexander Farber]

Hi again, I would like to share a slightly better workaround
(does not restart PostgreSQL) for enabling localhost connections
(for example for servlets) in a postgres:17-alpine3.20 based Dockerfile:

RUN sed -i "s/listen_addresses=''/listen_addresses='localhost'/"
/usr/local/bin/docker-entrypoint.sh

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Torsten Krah]

Am Sonntag, dem 01.12.2024 um 21:59 +0100 schrieb Alexander Farber:
> But I am yet not sure how to enable listening at localhost again

Instead of using a TCP/IP connection, why not use the unix socket to
connect to your database [1]?

[1] https://jdbc.postgresql.org/documentation/use/#unix-sockets

Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Bharani SV-forum]

 TeamPl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 
15.X 

Env = EC2 based Community PostgreSQL Ver 13.16.2 

we will be performing upgrade of our EC2 server too along with new OS.
Need help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X 
 ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2- ensure to 
capture all the pre.req meant for ver 15.10 are being met.
- shutdown db.- take offline full backup (PG_DATA folder alone)  using OS 
command
Proposed-new EC2 server (with new Operating System version along Postgres Ver 
15.10 Binaries)- install postgres 15.10 binaries- ensure to DISABLE auto 
startup and shutdown of postgres 15.10-  Restore offline full backup (PG_DATA 
folder alone) using OS command
-  start performing pg_upgrade step to upgrade postgres from ver 13.16.2 to 
15.10
please guide me, if i have missed any steps in the abovesaid process

To start new DB features, planning to rollout out the following feature's alone
a) TLE extension for password complianceb) parallelize vacuum jobs to utilize 
-j option

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Adrian Klaver]

On 12/2/24 14:18, Bharani SV-forum wrote:

Team
Pl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 
15.X


Env = EC2 based Community PostgreSQL Ver 13.16.2

we will be performing upgrade of our EC2 server too along with new OS.

Need help in vetting my steps for Postgres DB upgrade from Ver 13.X to 
ver 15.X

*ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
- ensure to capture all the pre.req meant for ver 15.10 are being met.
- shutdown db.
- take offline full backup (PG_DATA folder alone)  using OS command

*Proposed-new EC2 server (with new Operating System version along 
Postgres Ver 15.10 Binaries)*

- install postgres 15.10 binaries
- ensure to DISABLE auto startup and shutdown of postgres 15.10
-  Restore offline full backup (PG_DATA folder alone) using OS command
-  start performing pg_upgrade step to upgrade postgres from ver 13.16.2 


This is not going to work, you need to have Postgres 13 installed also.

It all laid out here:

https://www.postgresql.org/docs/15/pgupgrade.html

In Usage section.


to 15.10

please guide me, if i have missed any steps in the abovesaid process

To start new DB features, planning to rollout out the following 
feature's alone

a) TLE extension for password compliance
b) parallelize vacuum jobs to utilize -j option



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Adrian Klaver]

On 12/2/24 14:31, Ron Johnson wrote:
On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum 
mailto:esteembsv-fo...@yahoo.com>> wrote:


Team
Pl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to
ver 15.X

Env = EC2 based Community PostgreSQL Ver 13.16.2

we will be performing upgrade of our EC2 server too along with new OS.

Need help in vetting my steps for Postgres DB upgrade from Ver 13.X
to ver 15.X
*ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
- ensure to capture all the pre.req meant for ver 15.10 are being met.
- shutdown db.
- take offline full backup (PG_DATA folder alone)  using OS command

*Proposed-new EC2 server (with new Operating System version along
Postgres Ver 15.10 Binaries)*
- install postgres 15.10 binaries
- ensure to DISABLE auto startup and shutdown of postgres 15.10
-  Restore offline full backup (PG_DATA folder alone) using OS command
-  start performing pg_upgrade step to upgrade postgres from ver
13.16.2 to 15.10

please guide me, if i have missed any steps in the abovesaid process

To start new DB features, planning to rollout out the following
feature's alone
a) TLE extension for password compliance
b) parallelize vacuum jobs to utilize -j option


To migrate from one server to another while upgrading, one must use 
pg_dump/pg_restore OR Logical Replication.


Really?

Then this:

https://www.postgresql.org/docs/current/pgupgrade.html

must be random nose.



--
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Ron Johnson]

On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum 
wrote:

> Team
> Pl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver
> 15.X
>
> Env = EC2 based Community PostgreSQL Ver 13.16.2
>
> we will be performing upgrade of our EC2 server too along with new OS.
>
> Need help in vetting my steps for Postgres DB upgrade from Ver 13.X to
> ver 15.X
>
> *ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
> - ensure to capture all the pre.req meant for ver 15.10 are being met.
> - shutdown db.
> - take offline full backup (PG_DATA folder alone)  using OS command
>
> *Proposed-new EC2 server (with new Operating System version along Postgres
> Ver 15.10 Binaries)*
> - install postgres 15.10 binaries
> - ensure to DISABLE auto startup and shutdown of postgres 15.10
> -  Restore offline full backup (PG_DATA folder alone) using OS command
> -  start performing pg_upgrade step to upgrade postgres from ver 13.16.2
> to 15.10
>
> please guide me, if i have missed any steps in the abovesaid process
>
> To start new DB features, planning to rollout out the following feature's
> alone
> a) TLE extension for password compliance
> b) parallelize vacuum jobs to utilize -j option
>

To migrate from one server to another while upgrading, one must use
pg_dump/pg_restore OR Logical Replication.

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Adrian Klaver]

On 12/2/24 14:46, Adrian Klaver wrote:

On 12/2/24 14:31, Ron Johnson wrote:
On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum 
mailto:esteembsv-fo...@yahoo.com>> wrote:


    Team
    Pl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to
    ver 15.X

    Env = EC2 based Community PostgreSQL Ver 13.16.2

    we will be performing upgrade of our EC2 server too along with new 
OS.


    Need help in vetting my steps for Postgres DB upgrade from Ver 13.X
    to ver 15.X
    *ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
    - ensure to capture all the pre.req meant for ver 15.10 are being 
met.

    - shutdown db.
    - take offline full backup (PG_DATA folder alone)  using OS command

    *Proposed-new EC2 server (with new Operating System version along
    Postgres Ver 15.10 Binaries)*
    - install postgres 15.10 binaries
    - ensure to DISABLE auto startup and shutdown of postgres 15.10
    -  Restore offline full backup (PG_DATA folder alone) using OS 
command

    -  start performing pg_upgrade step to upgrade postgres from ver
    13.16.2 to 15.10

    please guide me, if i have missed any steps in the abovesaid process

    To start new DB features, planning to rollout out the following
    feature's alone
    a) TLE extension for password compliance
    b) parallelize vacuum jobs to utilize -j option


To migrate from one server to another while upgrading, one must use 
pg_dump/pg_restore OR Logical Replication.


Really?

Then this:

https://www.postgresql.org/docs/current/pgupgrade.html

must be random nose.


Oh yeah, that was smooth.

Second attempt:

... must be random noise.





--
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Errors when restoring backup created by pg_dumpall

[PopeRigby]

On 12/1/24 12:15, Tom Lane wrote:

Cool.  You did actually install the new scripts into your target
installation, right?


Oh, is applying the patch and rebuilding PostgreSQL not enough?

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Bharani SV-forum]

 Ron/AdrianThanks for your input.Your suggestion is
option#1 ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*-  
ensure to capture all the pre.req meant for ver 15.10 are being  met.
     - enable logical replication tagged to proposed new EC2 server (with newer 
Higher OS Version ).


Proposed-new EC2 server (with new Operating System version along
    Postgres Ver 13.16.2 and 15.10 Binaries)*     - install postgres 13.16.2 
binaries
    - have postgres setup on par with existing setup and having proper logical 
replication 
    - install postgres 15.10 binaries     - ensure to DISABLE auto startup and 
shutdown of postgres 13.16.2
      - ensure to DISABLE auto startup and shutdown of postgres 15.10    - 
start postgres db ver 13.16.2 and ensure all are good. no errors in postgres 
log file
    -  start performing pg_upgrade step to upgrade postgres from ver 13.16.2 to 
15.10
pl Vet the newer steps (revised version).

RegardsBharani
On Monday, December 2, 2024 at 05:48:19 PM EST, Adrian Klaver 
 wrote:  
 
 On 12/2/24 14:46, Adrian Klaver wrote:
> On 12/2/24 14:31, Ron Johnson wrote:
>> On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum 
>> mailto:esteembsv-fo...@yahoo.com>> wrote:
>>
>>     Team
>>     Pl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to
>>     ver 15.X
>>
>>     Env = EC2 based Community PostgreSQL Ver 13.16.2
>>
>>     we will be performing upgrade of our EC2 server too along with new 
>> OS.
>>
>>     Need help in vetting my steps for Postgres DB upgrade from Ver 13.X
>>     to ver 15.X
>>     *ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
>>     - ensure to capture all the pre.req meant for ver 15.10 are being 
>> met.
>>     - shutdown db.
>>     - take offline full backup (PG_DATA folder alone)  using OS command
>>
>>     *Proposed-new EC2 server (with new Operating System version along
>>     Postgres Ver 15.10 Binaries)*
>>     - install postgres 15.10 binaries
>>     - ensure to DISABLE auto startup and shutdown of postgres 15.10
>>     -  Restore offline full backup (PG_DATA folder alone) using OS 
>> command
>>     -  start performing pg_upgrade step to upgrade postgres from ver
>>     13.16.2 to 15.10
>>
>>     please guide me, if i have missed any steps in the abovesaid process
>>
>>     To start new DB features, planning to rollout out the following
>>     feature's alone
>>     a) TLE extension for password compliance
>>     b) parallelize vacuum jobs to utilize -j option
>>
>>
>> To migrate from one server to another while upgrading, one must use 
>> pg_dump/pg_restore OR Logical Replication.
> 
> Really?
> 
> Then this:
> 
> https://www.postgresql.org/docs/current/pgupgrade.html
> 
> must be random nose.

Oh yeah, that was smooth.

Second attempt:

... must be random noise.

> 
>>
>> -- 
>> Death to , and butter sauce.
>> Don't boil me, I'm still alive.
>>  lobster!
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Adrian Klaver]

On 12/2/24 15:41, Bharani SV-forum wrote:

Ron/Adrian
Thanks for your input.
Your suggestion is

*option#1*
  ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
-  ensure to capture all the pre.req meant for ver 15.10 are being  met.
      - enable logical replication tagged to proposed new EC2 server 


No, logical replication != pg_upgrade process.


(with newer Higher OS Version ).


Proposed-new EC2 server (with new Operating System version along
     Postgres Ver 13.16.2 and 15.10 Binaries)*
      - install postgres 13.16.2 binaries
     - have postgres setup on par with existing setup and having proper 
logical replication


Again, no.


     - install postgres 15.10 binaries
      - ensure to DISABLE auto startup and shutdown of postgres 13.16.2
       - ensure to DISABLE auto startup and shutdown of postgres 15.10
     - start postgres db ver 13.16.2 and ensure all are good. no errors 
in postgres log file
     -  start performing pg_upgrade step to upgrade postgres from ver 
13.16.2 to 15.10


pl Vet the newer steps (revised version).


I don't know how much clearer it can be, follow the step by step 
instructions shown here:


https://www.postgresql.org/docs/current/pgupgrade.html

"These are the steps to perform an upgrade with pg_upgrade:

[...]


"



Regards
Bharani

On Monday, December 2, 2024 at 05:48:19 PM EST, Adrian Klaver 
 wrote:



On 12/2/24 14:46, Adrian Klaver wrote:
 > On 12/2/24 14:31, Ron Johnson wrote:
 >> On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum
 >> mailto:esteembsv-fo...@yahoo.com> 
> wrote:

 >>
 >>     Team
 >>     Pl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to
 >>     ver 15.X
 >>
 >>     Env = EC2 based Community PostgreSQL Ver 13.16.2
 >>
 >>     we will be performing upgrade of our EC2 server too along with new
 >> OS.
 >>
 >>     Need help in vetting my steps for Postgres DB upgrade from Ver 13.X
 >>     to ver 15.X
 >>     *ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
 >>     - ensure to capture all the pre.req meant for ver 15.10 are being
 >> met.
 >>     - shutdown db.
 >>     - take offline full backup (PG_DATA folder alone)  using OS command
 >>
 >>     *Proposed-new EC2 server (with new Operating System version along
 >>     Postgres Ver 15.10 Binaries)*
 >>     - install postgres 15.10 binaries
 >>     - ensure to DISABLE auto startup and shutdown of postgres 15.10
 >>     -  Restore offline full backup (PG_DATA folder alone) using OS
 >> command
 >>     -  start performing pg_upgrade step to upgrade postgres from ver
 >>     13.16.2 to 15.10
 >>
 >>     please guide me, if i have missed any steps in the abovesaid process
 >>
 >>     To start new DB features, planning to rollout out the following
 >>     feature's alone
 >>     a) TLE extension for password compliance
 >>     b) parallelize vacuum jobs to utilize -j option
 >>
 >>
 >> To migrate from one server to another while upgrading, one must use
 >> pg_dump/pg_restore OR Logical Replication.
 >
 > Really?
 >
 > Then this:
 >
 > https://www.postgresql.org/docs/current/pgupgrade.html 


 >
 > must be random nose.

Oh yeah, that was smooth.

Second attempt:

... must be random noise.


 >
 >>
 >> --
 >> Death to , and butter sauce.
 >> Don't boil me, I'm still alive.
 >>  lobster!
 >

--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Bharani SV-forum]

 Adrian
Proposed new Server is intended to have higher OS Version (centos ver 9.0) and 
higher Postgres Version 15.10
Does logical replication will have issues , if the existing asis server is 
having Postgres ver 13.16.2 with Cent Os 7.0 with the new server having higher 
OS version Centos Ver 9.0 and then propose to have the Postgres to be upgraded 
from ver 13.16.2 to 15.10
Hope u have understood my question
On Monday, December 2, 2024 at 06:47:10 PM EST, Adrian Klaver 
 wrote:  
 
 On 12/2/24 15:41, Bharani SV-forum wrote:
> Ron/Adrian
> Thanks for your input.
> Your suggestion is
> 
> *option#1*
>   ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
> -  ensure to capture all the pre.req meant for ver 15.10 are being  met.
>       - enable logical replication tagged to proposed new EC2 server 

No, logical replication != pg_upgrade process.

> (with newer Higher OS Version ).
> 
> 
> Proposed-new EC2 server (with new Operating System version along
>      Postgres Ver 13.16.2 and 15.10 Binaries)*
>       - install postgres 13.16.2 binaries
>      - have postgres setup on par with existing setup and having proper 
> logical replication

Again, no.

>      - install postgres 15.10 binaries
>       - ensure to DISABLE auto startup and shutdown of postgres 13.16.2
>        - ensure to DISABLE auto startup and shutdown of postgres 15.10
>      - start postgres db ver 13.16.2 and ensure all are good. no errors 
> in postgres log file
>      -  start performing pg_upgrade step to upgrade postgres from ver 
> 13.16.2 to 15.10
> 
> pl Vet the newer steps (revised version).

I don't know how much clearer it can be, follow the step by step 
instructions shown here:

https://www.postgresql.org/docs/current/pgupgrade.html

"These are the steps to perform an upgrade with pg_upgrade:

[...]


"
> 
> 
> Regards
> Bharani
> 
> On Monday, December 2, 2024 at 05:48:19 PM EST, Adrian Klaver 
>  wrote:
> 
> 
> On 12/2/24 14:46, Adrian Klaver wrote:
>  > On 12/2/24 14:31, Ron Johnson wrote:
>  >> On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum
>  >> mailto:esteembsv-fo...@yahoo.com> 
> > wrote:
>  >>
>  >>     Team
>  >>     Pl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to
>  >>     ver 15.X
>  >>
>  >>     Env = EC2 based Community PostgreSQL Ver 13.16.2
>  >>
>  >>     we will be performing upgrade of our EC2 server too along with new
>  >> OS.
>  >>
>  >>     Need help in vetting my steps for Postgres DB upgrade from Ver 13.X
>  >>     to ver 15.X
>  >>     *ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
>  >>     - ensure to capture all the pre.req meant for ver 15.10 are being
>  >> met.
>  >>     - shutdown db.
>  >>     - take offline full backup (PG_DATA folder alone)  using OS command
>  >>
>  >>     *Proposed-new EC2 server (with new Operating System version along
>  >>     Postgres Ver 15.10 Binaries)*
>  >>     - install postgres 15.10 binaries
>  >>     - ensure to DISABLE auto startup and shutdown of postgres 15.10
>  >>     -  Restore offline full backup (PG_DATA folder alone) using OS
>  >> command
>  >>     -  start performing pg_upgrade step to upgrade postgres from ver
>  >>     13.16.2 to 15.10
>  >>
>  >>     please guide me, if i have missed any steps in the abovesaid process
>  >>
>  >>     To start new DB features, planning to rollout out the following
>  >>     feature's alone
>  >>     a) TLE extension for password compliance
>  >>     b) parallelize vacuum jobs to utilize -j option
>  >>
>  >>
>  >> To migrate from one server to another while upgrading, one must use
>  >> pg_dump/pg_restore OR Logical Replication.
>  >
>  > Really?
>  >
>  > Then this:
>  >
>  > https://www.postgresql.org/docs/current/pgupgrade.html 
> 
>  >
>  > must be random nose.
> 
> Oh yeah, that was smooth.
> 
> Second attempt:
> 
> ... must be random noise.
> 
> 
>  >
>  >>
>  >> --
>  >> Death to , and butter sauce.
>  >> Don't boil me, I'm still alive.
>  >>  lobster!
>  >
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 
> 
> 
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

VACUUM FULL, power failure results in unrecoverable space

[Pierre Barre]

Hello,

I encountered an issue while attempting to reclaim space from a heavily bloated 
table:

Initial analysis using 
https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql
 indicated approximately 600GB of bloat in the table.

I initiated a VACUUM FULL operation to reclaim this space, but during 
execution, the server experienced a power failure.


After server recovery:

1. The database came back online successfully
2. The space used by the in-progress table reconstruction was not freed
3. The newly wasted space (bloat + partial VACUUM FULL) is not shown using the 
above query, only the initial bloat.


Is there a way to reclaim this space without resorting to a full pg_dump and 
pg_restore cycle? 

Thanks.

Best,
Pierre

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Adrian Klaver]

On 12/2/24 15:52, Bharani SV-forum wrote:

Adrian

Proposed new Server is intended to have higher OS Version (centos ver 
9.0) and higher Postgres Version 15.10


Alright I did not catch this " ... with new OS" from your original post. 
I saw "Take offline full backup (PG_DATA folder alone)  using OS 
command" and "Restore offline full backup (PG_DATA folder alone) using 
OS command" and assumed like to like on the OS, my mistake.




Does logical replication will have issues , if the existing asis server 
is having Postgres ver 13.16.2 with Cent Os 7.0
with the new server having higher OS version Centos Ver 9.0 and then 
propose to have the Postgres to be upgraded

from ver 13.16.2 to 15.10


Logical replication would not have issue with this as that is one of 
it's use cases. The question now becomes whether that is the quickest/ 
most efficient way to do this.


That depends on:

1) What is the size of database(s) you are dealing with?

2) What sort of downtime can you afford?

3) EC2 --> EC2, are they the same region?




Hope u have understood my question

On Monday, December 2, 2024 at 06:47:10 PM EST, Adrian Klaver 
 wrote:




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Peter J. Holzer]

On 2024-12-03 00:25:15 +, Bharani SV-forum wrote:
> qsn1: What is the size of database(s) you are dealing with?
> ans1: roughly 25 GB  (maximum size)
> 
> qsn2 : What sort of downtime can you afford?
> ans2: can be maximum 30 mins or so

That does seem sufficient time to dump/restore a 25 GB database, so
that's what I'd try first. It's simple and you can easily test it
without disruption.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Ron Johnson]

Adrian,

OP is moving to a new VM when migrating to PG 15.  When was the
"cross-server" feature added to pg_upgrade?

On Mon, Dec 2, 2024 at 5:48 PM Adrian Klaver 
wrote:

> On 12/2/24 14:46, Adrian Klaver wrote:
> > On 12/2/24 14:31, Ron Johnson wrote:
> >> On Mon, Dec 2, 2024 at 5:18 PM Bharani SV-forum
> >> mailto:esteembsv-fo...@yahoo.com>> wrote:
> >>
> >> Team
> >> Pl Help in vetting my steps for Postgres DB upgrade from Ver 13.X to
> >> ver 15.X
> >>
> >> Env = EC2 based Community PostgreSQL Ver 13.16.2
> >>
> >> we will be performing upgrade of our EC2 server too along with new
> >> OS.
> >>
> >> Need help in vetting my steps for Postgres DB upgrade from Ver 13.X
> >> to ver 15.X
> >> *ASIS-existing server = EC2 with Community PostgreSQL Ver 13.16.2*
> >> - ensure to capture all the pre.req meant for ver 15.10 are being
> >> met.
> >> - shutdown db.
> >> - take offline full backup (PG_DATA folder alone)  using OS command
> >>
> >> *Proposed-new EC2 server (with new Operating System version along
> >> Postgres Ver 15.10 Binaries)*
> >> - install postgres 15.10 binaries
> >> - ensure to DISABLE auto startup and shutdown of postgres 15.10
> >> -  Restore offline full backup (PG_DATA folder alone) using OS
> >> command
> >> -  start performing pg_upgrade step to upgrade postgres from ver
> >> 13.16.2 to 15.10
> >>
> >> please guide me, if i have missed any steps in the abovesaid process
> >>
> >> To start new DB features, planning to rollout out the following
> >> feature's alone
> >> a) TLE extension for password compliance
> >> b) parallelize vacuum jobs to utilize -j option
> >>
> >>
> >> To migrate from one server to another while upgrading, one must use
> >> pg_dump/pg_restore OR Logical Replication.
> >
> > Really?
> >
> > Then this:
> >
> > https://www.postgresql.org/docs/current/pgupgrade.html
> >
> > must be random nose.
>
> Oh yeah, that was smooth.
>
> Second attempt:
>
> ... must be random noise.
>
> >
> >>
> >> --
> >> Death to , and butter sauce.
> >> Don't boil me, I'm still alive.
> >>  lobster!
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: VACUUM FULL, power failure results in unrecoverable space

[Guillaume Lelarge]

Hi,


Le mar. 3 déc. 2024, 01:02, Pierre Barre  a écrit :

> Hello,
>
> I encountered an issue while attempting to reclaim space from a heavily
> bloated table:
>
> Initial analysis using
> https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql
>  indicated
> approximately 600GB of bloat in the table.
>
> I initiated a VACUUM FULL operation to reclaim this space, but during
> execution, the server experienced a power failure.
>
>
> After server recovery:
>
> 1. The database came back online successfully
> 2. The space used by the in-progress table reconstruction was not freed
>

Because doesn't know it's here.

3. The newly wasted space (bloat + partial VACUUM FULL) is not shown using
> the above query, only the initial bloat.
>

Because it's not the same kind of bloat. Vacuum full builds another set of
files for the table, and these files aren't yet connected to the table,
since vacuum full didn't finish. You've got some orphaned files you'll have
to delete.


>
> Is there a way to reclaim this space without resorting to a full pg_dump
> and pg_restore cycle?
>

You can dump and restore (which will drop all bloat of this database), but
will probably take too long to finish. You can also see pg_orphaned to know
which files to delete (https://github.com/bdrouvot/pg_orphaned).

Regards.


-- 
Guillaume.

Re: Errors when restoring backup created by pg_dumpall

[Tom Lane]

PopeRigby  writes:
> On 12/1/24 12:15, Tom Lane wrote:
>> Cool.  You did actually install the new scripts into your target
>> installation, right?

> Oh, is applying the patch and rebuilding PostgreSQL not enough?

Not unless you did "make install" in the contrib/earthdistance
directory (or something wider-scope that would invoke that).

regards, tom lane

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Bharani SV-forum]

 AdrianNoted about, Logical replication would not have issue with this as that 
is one of
it's use cases.

qsn1: What is the size of database(s) you are dealing with?
ans1: roughly 25 GB  (maximum size)

qsn2 : What sort of downtime can you afford?
ans2: can be maximum 30 mins or so
qsn3: EC2 --> EC2, are they the same region?ans3: Right Question. I assume the 
same region
Can you pl provide your insight now 
On Monday, December 2, 2024 at 07:20:52 PM EST, Adrian Klaver 
 wrote:  
 
 On 12/2/24 15:52, Bharani SV-forum wrote:
> Adrian
> 
> Proposed new Server is intended to have higher OS Version (centos ver 
> 9.0) and higher Postgres Version 15.10

Alright I did not catch this " ... with new OS" from your original post. 
I saw "Take offline full backup (PG_DATA folder alone)  using OS 
command" and "Restore offline full backup (PG_DATA folder alone) using 
OS command" and assumed like to like on the OS, my mistake.

> 
> Does logical replication will have issues , if the existing asis server 
> is having Postgres ver 13.16.2 with Cent Os 7.0
> with the new server having higher OS version Centos Ver 9.0 and then 
> propose to have the Postgres to be upgraded
> from ver 13.16.2 to 15.10

Logical replication would not have issue with this as that is one of 
it's use cases. The question now becomes whether that is the quickest/ 
most efficient way to do this.

That depends on:

1) What is the size of database(s) you are dealing with?

2) What sort of downtime can you afford?

3) EC2 --> EC2, are they the same region?


> 
> Hope u have understood my question
> 
> On Monday, December 2, 2024 at 06:47:10 PM EST, Adrian Klaver 
>  wrote:
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com

  

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Adrian Klaver]

On 12/2/24 17:23, Ron Johnson wrote:

Adrian,

OP is moving to a new VM when migrating to PG 15.  When was the 
"cross-server" feature added to pg_upgrade?




Moving to a new VM was not the issue, my mistake was thinking the OS 
version was staying the same.


Then:

On old VM:

"take offline full backup (PG_DATA folder alone)  using OS command"

On new VM:
"Restore offline full backup (PG_DATA folder alone) using OS command"

Followed by installing new Postgres version could be dealt with using 
pg_upgrade. Once I was corrected on what was actually going on then 
doing a dump/restore or logical replication became better choices.



--
Adrian Klaver
adrian.kla...@aklaver.com