Thread: Create a standby server

Hello,
I have a master and tow standby servers. I want to create another one. These servers are made by someone else and I am
anewbie in PostgreSQL. 
I found the following two tutorials:

https://linuxconfig.org/how-to-create-a-hot-standby-with-postgresql

https://github.com/GoogleCloudPlatform/community/blob/master/archived/setting-up-postgres-hot-standby.md

A) Which on is better and easier?

B) In these articles, to create a Standby server, a user is created in the database. Because there are already two
Standbyservers, this user is probably created. How can I find it? Can I use that user to build a third server? 


Cheers.

Greetings,

* normandavis1990 (normandavis1990@proton.me) wrote:
> I have a master and tow standby servers. I want to create another one. These servers are made by someone else and I
ama newbie in PostgreSQL. 
> I found the following two tutorials:

[...]

> A) Which on is better and easier?

One referred to 9.2, which is extremly old and no longer supported, and
the other said it was archived ... so I'm not sure either is really
great to be used today.

> B) In these articles, to create a Standby server, a user is created in the database. Because there are already two
Standbyservers, this user is probably created. How can I find it? Can I use that user to build a third server? 

If those systems are connected to the primary, you can query the view
pg_stat_replication and see what user they are connected with:

SELECT * FROM pg_stat_replication;

You should be able to use the existing user to create a new standby.
I'd recommend using pg_basebackup to create it with a command along
these lines:

pg_basebackup -h existing.server.com -U username -D /destination/directory -c fast -C -S standbyslotname -R -P -v

Running pg_basebackup this way will:
- Have pg_basebackup connect to 'existing.server.com' (should be your
  primary)
- Connect as user 'username' (pull this from the 'usename' field in
  pg_stat_replication)
- Store the data files for the new system into /destination/directory on
  the system where pg_basebackup is run
- Start the backup immediately by doing a 'fast' checkpoint
- Create a replication slot to use to make sure the WAL is kept on the
  primary until the new standby system collects it (you should monitor
  this though- if you destroy this new system, WAL could build up on the
  primary).
- Use 'standbyslotname' as the name of the slot that's created
- Instructs pg_basebackup to write out the connection information to
  connect to the primary and start streaming when it starts up.
- Enabled progress reporting from pg_basebackup
- Enables verbose mode of pg_basebackup

Full documentation of pg_basebackup is here:

https://www.postgresql.org/docs/current/app-pgbasebackup.html

Thanks!

Stephen

> On Monday, March 11th, 2024 at 3:43 PM, Stephen Frost <sfrost@snowman.net> wrote:

> Greetings,
>
> * normandavis1990 (normandavis1990@proton.me) wrote:
>
> > I have a master and tow standby servers. I want to create another one. These servers are made by someone else and I
ama newbie in PostgreSQL. 
> > I found the following two tutorials:
>
>
> [...]
>
> > A) Which on is better and easier?
>
>
> One referred to 9.2, which is extremly old and no longer supported, and
> the other said it was archived ... so I'm not sure either is really
> great to be used today.
>
> > B) In these articles, to create a Standby server, a user is created in the database. Because there are already two
Standbyservers, this user is probably created. How can I find it? Can I use that user to build a third server? 
>
>
> If those systems are connected to the primary, you can query the view
> pg_stat_replication and see what user they are connected with:
>
> SELECT * FROM pg_stat_replication;
>
> You should be able to use the existing user to create a new standby.
> I'd recommend using pg_basebackup to create it with a command along
> these lines:
>
> pg_basebackup -h existing.server.com -U username -D /destination/directory -c fast -C -S standbyslotname -R -P -v
>
> Running pg_basebackup this way will:
> - Have pg_basebackup connect to 'existing.server.com' (should be your
> primary)
> - Connect as user 'username' (pull this from the 'usename' field in
> pg_stat_replication)
> - Store the data files for the new system into /destination/directory on
> the system where pg_basebackup is run
> - Start the backup immediately by doing a 'fast' checkpoint
> - Create a replication slot to use to make sure the WAL is kept on the
> primary until the new standby system collects it (you should monitor
> this though- if you destroy this new system, WAL could build up on the
> primary).
> - Use 'standbyslotname' as the name of the slot that's created
> - Instructs pg_basebackup to write out the connection information to
> connect to the primary and start streaming when it starts up.
> - Enabled progress reporting from pg_basebackup
> - Enables verbose mode of pg_basebackup
>
> Full documentation of pg_basebackup is here:
>
> https://www.postgresql.org/docs/current/app-pgbasebackup.html
>
> Thanks!
>
> Stephen

Hi,
Thank you.
Shoudd I run the following command on the mater?

$ pg_basebackup -h "Master_IP_Address" -U username -D /destination/directory -c fast -C -S standbyslotname -R -P -v