Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjeXT-006xEe-Bw for pgsql-general@arkaria.postgresql.org; Mon, 11 Mar 2024 12:13:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rjeXR-00D59S-Vt for pgsql-general@arkaria.postgresql.org; Mon, 11 Mar 2024 12:13:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjeXR-00D59K-KN for pgsql-general@lists.postgresql.org; Mon, 11 Mar 2024 12:13:54 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjeXO-003va1-UD for pgsql-general@lists.postgresql.org; Mon, 11 Mar 2024 12:13:53 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id B0EA75F7B7; Mon, 11 Mar 2024 08:13:45 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1710159225; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=46F2FDeARPkgesh0goT7paI9IhnTNoAp286BvIgX8jQ=; b=rI6ml8IW6cW5VzHTj9/HcYYACyQA44vbwB/VoeqmV5c0SSocQ+8CHPEZI3RnhosKUHC8ta PSuVUQDV23VCfNse+4zoh+qlRVT6ljFZtiOBw38VJkHprFXqx0Ue8PV2uTGvO2cbeRRS/H GQ3Jdq5xYFHpTc9PqSQgt3Cih9tNAuU= Date: Mon, 11 Mar 2024 08:13:45 -0400 From: Stephen Frost To: normandavis1990 Cc: "pgsql-general@lists.postgresql.org" Subject: Re: Create a standby server Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="s6CiBU8G4HEoMNP0" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/2.1.4 (2021-12-11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --s6CiBU8G4HEoMNP0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * normandavis1990 (normandavis1990@proton.me) wrote: > I have a master and tow standby servers. I want to create another one.=A0= These servers are made by someone else and=A0I 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 th= e database. Because there are already two Standby servers, this user is pro= bably created. How can I find it? Can I use that user to build a third serv= er? 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 --s6CiBU8G4HEoMNP0 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmXu9XcACgkQ7WyKOINH ZFUXqBAArh5C74fOOXhZL9ZR0EAIaBQeZe6R6BYn0r+dz29ZdI2FM5SjNkYnBgXH jPqTUZuS1qd3vDUQBGqpemOmUdj8sKYEbh2R6iRIU7Oz2V4Y5jPog96gm9kg6zwO 8RpzabRSTMXPwepnzH0WGjmLuSVYVw1HQc2lnKj3we3GVs2mXDhNpM7+jlSE5fyI 5fP/b3pHZWQrOle5evif2zoyyB9GaaUc7CoZAf52y5RyGO6APOQdNgk7q8ILBPZn GgLXOV3uzIhUpMS2vEtY7dfzn1eWy6vJHn4yaYsvRE9FmIttNYynccwqjII/x7Fa 31nnLcGp7HpIjl0a1EN2V30XXamTJhsMKJop+G/HLjlbjWGZ84L7jVa7bbD7ne8u vFzMEHQmlukI4ah64VC8EL5C8/NHF8Xcui7LgjVi1vNPHYYoZRy8Ou73GmPuw2lf jBKpiqNHxTjpkJ3uGYCKZcrpw/onTuG4mc+uBo3fJefNbmL7K9YqV1jp0oEkAzJE d3zCuofDWS5XA5nhGeHz6XIMMMGDXyEcSvLfXrsQJIv0BMVfYe33lWabJBLAF2/X 6sQKMQckEsf6nIiGuyhgOc2bkghMaNBeL1NAV8tm0Ic4NDCOiRsWISRII4c3wZ9j F7CNfnlVQskxzzd7yMNOZqpCz+dUEy7zqsV47CeVohFdRgCAL0A= =lZmj -----END PGP SIGNATURE----- --s6CiBU8G4HEoMNP0--