pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedCreate a standby server
6+ messages / 3 participants
[nested] [flat]
* Create a standby server
@ 2024-03-11 11:51 normandavis1990 <normandavis1990@proton.me>
0 siblings, 2 replies; 6+ messages in thread
From: normandavis1990 @ 2024-03-11 11:51 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 a newbie 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-standb...
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 Standby servers, this user is probably created. How can I find it? Can I use that user to build a third server?
Cheers.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Create a standby server
@ 2024-03-11 12:09 Mateusz Henicz <mateuszhenicz@gmail.com>
parent: normandavis1990 <normandavis1990@proton.me>
1 sibling, 1 reply; 6+ messages in thread
From: Mateusz Henicz @ 2024-03-11 12:09 UTC (permalink / raw)
To: normandavis1990 <normandavis1990@proton.me>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hey,
Check your parameter primary_conninfo on any standby server, you should
find here information about the user used for replication and its password
or path to .pgpass file, where the password is stored. If there is no
password or .pgpass file defined, then you do not need any password most
likely, and you are likely using the "trust" authentication method in your
pg_hba.conf for replication.
To create a replica pretty much all you have to do is to add your new
standby server to pg_hba.conf, so you are allowed to connect and run on
your new standby:
pg_basebackup -h <primary IP address> -U <replicator_user> -R -D /data_pg
-X stream
and then start it using
pg_ctl -D /data_pg start
There can be some parameters that need to be adjusted, like
listen_addresses for example, unless you are using "*" for it. And maybe
max_wal_senders/max_replication_slots can be too low and you may have to
increase it, but if there is any problem and you will try to start your new
replica it will just fail and you will get information about what was wrong
to your logfile, so it is easy to find.
By adding -R to pg_basebackup you will get your replication configuration
generated automatically to postgresql.auto.conf and -X will stream all WAL
files generated during pg_basebackup execution to your new replica server.
If you prefer to use replication slots you may also add -C -S <slot_name>
to get a replication slot created automatically by pg_basebackup.
Good luck!
Cheers,
Mateusz
pon., 11 mar 2024 o 12:51 normandavis1990 <normandavis1990@proton.me>
napisał(a):
> 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 a newbie 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-standb...
>
> 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 Standby servers, this user is
> probably created. How can I find it? Can I use that user to build a third
> server?
>
>
> Cheers.
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Create a standby server
@ 2024-03-11 12:13 Stephen Frost <sfrost@snowman.net>
parent: normandavis1990 <normandavis1990@proton.me>
1 sibling, 1 reply; 6+ messages in thread
From: Stephen Frost @ 2024-03-11 12:13 UTC (permalink / raw)
To: normandavis1990 <normandavis1990@proton.me>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 am a 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 Standby servers, 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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Create a standby server
@ 2024-03-11 12:32 normandavis1990 <normandavis1990@proton.me>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 6+ messages in thread
From: normandavis1990 @ 2024-03-11 12:32 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> 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 am a 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 Standby servers, 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Create a standby server
@ 2024-03-11 12:33 normandavis1990 <normandavis1990@proton.me>
parent: Mateusz Henicz <mateuszhenicz@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: normandavis1990 @ 2024-03-11 12:33 UTC (permalink / raw)
To: Mateusz Henicz <mateuszhenicz@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> On Monday, March 11th, 2024 at 3:39 PM, Mateusz Henicz <mateuszhenicz@gmail.com> wrote:
> Hey,
> Check your parameter primary_conninfo on any standby server, you should find here information about the user used for replication and its password or path to .pgpass file, where the password is stored. If there is no password or .pgpass file defined, then you do not need any password most likely, and you are likely using the "trust" authentication method in your pg_hba.conf for replication.
>
> To create a replica pretty much all you have to do is to add your new standby server to pg_hba.conf, so you are allowed to connect and run on your new standby:
> pg_basebackup -h <primary IP address> -U <replicator_user> -R -D /data_pg -X stream
> and then start it using
> pg_ctl -D /data_pg start
>
> There can be some parameters that need to be adjusted, like listen_addresses for example, unless you are using "*" for it. And maybe max_wal_senders/max_replication_slots can be too low and you may have to increase it, but if there is any problem and you will try to start your new replica it will just fail and you will get information about what was wrong to your logfile, so it is easy to find.
>
> By adding -R to pg_basebackup you will get your replication configuration generated automatically to postgresql.auto.conf and -X will stream all WAL files generated during pg_basebackup execution to your new replica server.
> If you prefer to use replication slots you may also add -C -S <slot_name> to get a replication slot created automatically by pg_basebackup.
>
> Good luck!
>
> Cheers,
> Mateusz
>
> pon., 11 mar 2024 o 12:51 normandavis1990 <normandavis1990@proton.me> napisał(a):
>
>> 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 a newbie 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-standb...
>>
>> 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 Standby servers, this user is probably created. How can I find it? Can I use that user to build a third server?
>>
>> Cheers.
Hi,
Should I run those commands on the standby server?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Create a standby server
@ 2024-03-11 14:19 Stephen Frost <sfrost@snowman.net>
parent: normandavis1990 <normandavis1990@proton.me>
0 siblings, 0 replies; 6+ messages in thread
From: Stephen Frost @ 2024-03-11 14:19 UTC (permalink / raw)
To: normandavis1990 <normandavis1990@proton.me>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Greetings,
On Mon, Mar 11, 2024 at 13:33 normandavis1990 <normandavis1990@proton.me>
wrote:
> > 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 am a 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 Standby servers, 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
No, on the system you wish to bring up as another standby.
Thanks,
Stephen
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-03-11 14:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-11 11:51 Create a standby server normandavis1990 <normandavis1990@proton.me>
2024-03-11 12:09 ` Mateusz Henicz <mateuszhenicz@gmail.com>
2024-03-11 12:33 ` normandavis1990 <normandavis1990@proton.me>
2024-03-11 12:13 ` Stephen Frost <sfrost@snowman.net>
2024-03-11 12:32 ` normandavis1990 <normandavis1990@proton.me>
2024-03-11 14:19 ` Stephen Frost <sfrost@snowman.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox