Moving PostgreSQL servers to a new server
Hi, I am new to PostgreSQL. My scenario is as follows: https://cdn.bloghunch.com/uploads/uNxNoi5uVKeDibd5.webp I have a Master server and two Replica (Replica-1 and Replica-2) servers. One of these Replica servers is in read-only mode and a report is prepared from it using Microsoft PowerBI. Due to a series of issues, I want to transfer the Master server and one of the Replica servers to other servers in two steps, but I don't want to have any interruptions. A) Assuming PostgreSQL is installed on the new server, what should I do first to migrate the Master? Please show me a tutorial and share your experiences. B) After transferring the Master, I want to transfer one of the Replica servers. What should I do to transfer this server? Cheers.
High Availability and Replication
Hello, What is the difference between High Availability and Replication? Cheers.
Re: High Availability and Replication
What does this technique do? On Thursday, February 29th, 2024 at 10:45 PM, David G. Johnston wrote: > On Thursday, February 29, 2024, normandavis1990 > wrote: > >> What is the difference between High Availability and Replication? > > The former is a goal, the later is a technique. > > David J.
How to clone a database?
Hello, I have a primary server with two replication servers. I want to HA this primary server because I need to shut it down. Please introduce me to a tutorial that explains this step by step. Could setting up HA cause the primary server to go down? Cheers.
Re: High Availability and Replication
> On Thursday, February 29th, 2024 at 11:38 PM, Israel Brewster > wrote: >> On Feb 29, 2024, at 10:15 AM, David G. Johnston >> wrote: >> >> On Thursday, February 29, 2024, normandavis1990 >> wrote: >> >>> What is the difference between High Availability and Replication? >> >> The former is a goal, the later is a technique. > > Perhaps more specifically: Replication is simply Replicating - or copying - > the “master” database to one or more “slave” databases, generally in > real-time such that the slave database clusters are replicas of the master. > This is good when the master goes down, because you’ll still have one or more > copies of it available, but by itself it doesn’t keep there from being an > outage if/when the master goes down. > > High Availability layers on top of replication to provide some means of > ensuring that the database is HIGHLY available, such as an automatic failover > system or load balancer. Many different options that work in many different > ways are available to help meet this goal. > --- > Israel Brewster > Software Engineer > Alaska Volcano Observatory > Geophysical Institute - UAF > 2156 Koyukuk Drive > Fairbanks AK 99775-7320 > Work: 907-474-5172 > cell: 907-328-9145 > >> David J. Hi, You said "This is good when the master goes down, because you’ll still have one or more copies of it available, but by itself it doesn’t keep there from being an outage if/when the master goes down.". What does "goes down" mean? In Replication mode, if the primary server is shut down, then the data will also be lost?
Re: High Availability and Replication
> On Saturday, March 2nd, 2024 at 12:14 AM, Israel Brewster > wrote: >> On Mar 1, 2024, at 11:36 AM, normandavis1990 >> wrote: >> >>> On Thursday, February 29th, 2024 at 11:38 PM, Israel Brewster >>> wrote: >> >>>> On Feb 29, 2024, at 10:15 AM, David G. Johnston >>>> wrote: >>>> >>>> On Thursday, February 29, 2024, normandavis1990 >>>> wrote: >>>> >>>>> What is the difference between High Availability and Replication? >>>> >>>> The former is a goal, the later is a technique. >>> >>> Perhaps more specifically: Replication is simply Replicating - or copying - >>> the “master” database to one or more “slave” databases, generally in >>> real-time such that the slave database clusters are replicas of the master. >>> This is good when the master goes down, because you’ll still have one or >>> more copies of it available, but by itself it doesn’t keep there from being >>> an outage if/when the master goes down. >>> >>> High Availability layers on top of replication to provide some means of >>> ensuring that the database is HIGHLY available, such as an automatic >>> failover system or load balancer. Many different options that work in many >>> different ways are available to help meet this goal. >>> --- >>> Israel Brewster >>> Software Engineer >>> Alaska Volcano Observatory >>> Geophysical Institute - UAF >>> 2156 Koyukuk Drive >>> Fairbanks AK 99775-7320 >>> Work: 907-474-5172 >>> cell: 907-328-9145 >>> >>>> David J. >> >> Hi, >> You said "This is good when the master goes down, because you’ll still have >> one or more copies of it available, but by itself it doesn’t keep there from >> being an outage if/when the master goes down.". What does "goes down" mean? > > Exactly what I said - it goes down. Not functioning. Offline. Inaccessible. > It is not up and running, therefore, it is down. > >> In Replication mode, if the primary server is shut down, then the data will >> also be lost? > > No. As I said - and you quoted - “When the master goes down…you’ll still have > one or more copies of it available”. So no, the data will NOT be lost. > > --- > Israel Brewster > Software Engineer > Alaska Volcano Observatory > Geophysical Institute - UAF > 2156 Koyukuk Drive > Fairbanks AK 99775-7320 > Work: 907-474-5172 > cell: 907-328-9145 Hi, Therefore, in both HA and Replication, when the primary server is shut down, the information is not lost, and the only difference is that in the HA mechanism, another server replaces the primary one, but this is not the case in Replication. Is it true?
Re: High Availability and Replication
> On Saturday, March 2nd, 2024 at 1:24 AM, Abdul Sayeed > wrote: > Hi, > > When Master server goes down, either you need to promote one of slave node or > configure HA mechanism so that in case of master server goes down it will > automatically promote the slave server as new master. > > Patroni HA tool would be good option for your requirement. > > https://patroni.readthedocs.io/en/latest/README.html > > Hope this helps. > > Thanks & Regards, > Abdul Sayeed > PostgreSQL DBA > > On Sat, 2 Mar 2024 at 2:15 AM, Israel Brewster wrote: > >>> On Mar 1, 2024, at 11:36 AM, normandavis1990 >>> wrote: >>> >>>> On Thursday, February 29th, 2024 at 11:38 PM, Israel Brewster >>>> wrote: >>> >>>>> On Feb 29, 2024, at 10:15 AM, David G. Johnston >>>>> wrote: >>>>> >>>>> On Thursday, February 29, 2024, normandavis1990 >>>>> wrote: >>>>> >>>>>> What is the difference between High Availability and Replication? >>>>> >>>>> The former is a goal, the later is a technique. >>>> >>>> Perhaps more specifically: Replication is simply Replicating - or copying >>>> - the “master” database to one or more “slave” databases, generally in >>>> real-time such that the slave database clusters are replicas of the >>>> master. This is good when the master goes down, because you’ll still have >>>> one or more copies of it available, but by itself it doesn’t keep there >>>> from being an outage if/when the master goes down. >>>> >>>> High Availability layers on top of replication to provide some means of >>>> ensuring that the database is HIGHLY available, such as an automatic >>>> failover system or load balancer. Many different options that work in many >>>> different ways are available to help meet this goal. >>>> --- >>>> Israel Brewster >>>> Software Engineer >>>> Alaska Volcano Observatory >>>> Geophysical Institute - UAF >>>> [2156 Koyukuk >>>> Drive](https://www.google.com/maps/search/2156+Koyukuk+Drive+Fairbanks+AK+99775-7320?entry=gmail) >>>> [Fairbanks AK >>>> 99775-7320](https://www.google.com/maps/search/2156+Koyukuk+Drive+Fairbanks+AK+99775-7320?entry=gmail) >>>> Work: 907-474-5172 >>>> cell: 907-328-9145 >>>> >>>>> David J. >>> >>> Hi, >>> You said "This is good when the master goes down, because you’ll still have >>> one or more copies of it available, but by itself it doesn’t keep there >>> from being an outage if/when the master goes down.". What does "goes down" >>> mean? >> >> Exactly what I said - it goes down. Not functioning. Offline. Inaccessible. >> It is not up and running, therefore, it is down. >> >>> In Replication mode, if the primary server is shut down, then the data will >>> also be lost? >> >> No. As I said - and you quoted - “When the master goes down…you’ll still >> have one or more copies of it available”. So no, the data will NOT be lost. >> >> --- >> Israel Brewster >> Software Engineer >> Alaska Volcano Observatory >> Geophysical Institute - UAF >> [2156 Koyukuk >> Drive](https://www.google.com/maps/search/2156+Koyukuk+Drive+Fairbanks+AK+99775-7320?entry=gmail) >> [Fairbanks AK >> 99775-7320](https://www.google.com/maps/search/2156+Koyukuk+Drive+Fairbanks+AK+99775-7320?entry=gmail) >> Work: 907-474-5172 >> cell: 907-328-9145 Hi, Does installing Patroni cause the primary server to stop even for a short time?
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 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-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 Standby servers, this user is probably created. How can I find it? Can I use that user to build a third server? Cheers.
Re: Create a standby server
> On Monday, March 11th, 2024 at 3:43 PM, Stephen Frost > wrote: > Greetings, > > * normandavis1990 (normandavis1...@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
Re: Create a standby server
> On Monday, March 11th, 2024 at 3:39 PM, Mateusz Henicz > 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 -U -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 to > get a replication slot created automatically by pg_basebackup. > > Good luck! > > Cheers, > Mateusz > > pon., 11 mar 2024 o 12:51 normandavis1990 > 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-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 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?
