Re: archive command in streaming replication in windows server
I would try.. Read the server log Try copying to another simpler path like C:\wal_archives Maybe blankspaces give problems Have you forgotten to configure any other parameter like max_wal_senders? I hope some of this hemos you Loles El jue., 29 oct. 2020 20:36, Atul Kumar escribió: > hi, > > Still it is not getting copied in the archive directory > > show archive_command > "copy "%p" "C:\Program Files\PostgreSQL\10\archive_files\%f"" > > in postgresql.conf > archive_command = 'copy "%p" "C:\\Program > Files\\PostgreSQL\\10\\archive_files\\%f"' > > > and when I restarted the postgres service using command prompt I am > getting the below > alert in cmd > C:\Program Files\PostgreSQL\10\bin>0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > 0 file(s) copied. > > > > > Please help. > > > > Regards, > Atul > > > > > > > > > > > > > > > > On 10/30/20, Andreas Kretschmer wrote: > > > > > > Am 29.10.20 um 20:12 schrieb Atul Kumar: > >> hi, > >> > >> I am trying to configure streaming replication on windows server. > >> > >> i have postgres version 10 > >> > >> after successful installation of postgres, I create a archive > >> directory "C:\Program Files\PostgreSQL\10\archive_files" and here in > >> archive_flies, I need to copy the wal files. > >> > >> in postgresql conf file I gave path in archive_command like this: > >> > >> 'copy %p C:\Program Files\PostgreSQL\10\archive_files\%f' > >> > >> After successfully restarting the postgres service, I could not find > >> any file in the archive directory. > >> > >> and when I am giving command > >> show archive_command > >> > >> I get this as an output > >> > >> "copy %p C:Program FilesPostgreSQL archive_files%f" > >> > >> So please help in setting me the correct path format. > > > > from the fine documentation an example: > > > > archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p > > /mnt/server/archivedir/%f' # Unix > > archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows > > > > > > https://www.postgresql.org/docs/current/continuous-archiving.html > > > > > > Regards, Andreas > > > > -- > > 2ndQuadrant - The PostgreSQL Support Company. > > www.2ndQuadrant.com > > > > > > > > > > >
Re: Replication sequence
One thing is the number of records in the table and another is the current value of the sequence. Execute: select max (field) from bobo.menu_pages; In both master and slave server. What give you back? "field" is the data that belongs to the sequence. El lun., 15 feb. 2021 18:55, Paolo Saudin escribió: > Hi all, > I have two servers, a primary and a secondary one with a streaming replica > setup. > Today I noticed that some sequences are not lined-up, the replica ones are > well ahead, while the records number is the same. How is it possible? > > Thanks, > Paolo > > -- > -- primary server > -- > postgres@db-srv1:~$ psql > psql (9.5.19) > cf-db-srv1:5432 postgres@rete_llpp=# SELECT last_value FROM > bobo.menu_pages_mp_id_seq; > ┌┐ > │ last_value │ > ├┤ > │ 74 │ > └┘ > cf-db-srv1:5432 postgres@rete_llpp=# select count(*) from > bobo.menu_pages; > ┌───┐ > │ count │ > ├───┤ > │74 │ > └───┘ > > -- > -- replica server > -- > postgres@db-srv2:~$ psql > psql (9.5.24) > cf-db-srv2:5433 postgres@rete_llpp=# SELECT last_value FROM > bobo.menu_pages_mp_id_seq; > ┌┐ > │ last_value │ > ├┤ > │105 │ > └┘ > cf-db-srv2:5433 postgres@rete_llpp=# select count(*) from bobo.menu_pages; > ┌───┐ > │ count │ > ├───┤ > │74 │ > └───┘ > >
Two started cluster on the same data directory and port
Hi! I have this situation on a server that is giving me problems. postgres@hostname:~$ pg_lsclusters Ver Cluster Port Status OwnerData directory Log file 14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log 14 main_old 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main_old.log How can I stop, even delete, the main_old cluster? Thanks!
Re: Two started cluster on the same data directory and port
Every time I stop the main_old cluster with sudo pg_ctlcluster stop 14 main_old, both are stopped. And when I raise sudo pg_ctlcluster start 14 main, both are raised. I don't know how I got into this situation and I don't know how to resolve it. El jue, 30 nov 2023 a las 17:34, Loles () escribió: > Indeed, that is the problem, they are two different clusters, pointing to > the same data directory and reading from the same port. > > I can't change the port because it is the same postgresql.conf > > I don't know how to disassociate the main_old cluster from the data > directory or how to resolve this situation. > > El jue, 30 nov 2023 a las 16:32, Adrian Klaver () > escribió: > >> On 11/30/23 07:23, Adrian Klaver wrote: >> > On 11/30/23 05:37, Loles wrote: >> >> Hi! >> >> >> >> I have this situation on a server that is giving me problems. >> >> >> >> postgres@hostname:~$ pg_lsclusters >> >> Ver Cluster Port Status OwnerData directory Log file >> >> 14 main 5432 online postgres /var/lib/postgresql/14/main >> >> /var/log/postgresql/postgresql-14-main.log >> >> 14 main_old 5432 online postgres /var/lib/postgresql/14/main >> >> /var/log/postgresql/postgresql-14-main_old.log >> >> >> >> How can I stop, even delete, the main_old cluster? >> > >> > Assuming you are on Debian/Ubuntu. >> > >> > 1) sudo pg_ctlcluster stop 14 main_old >> > >> > This will stop the server for this session >> > >> > 2) sudo vi /etc/postgresql/14/main_old/start.conf >> > >> > Change auto to manual. >> > This will keep the server from restarting on reboot. >> > You can still start it using >> > sudo pg_ctlcluster start 14 main_old >> > Though if you want to do that then: >> > vi /etc/postgresql/14/main_old/postgresql.conf >> > and change port to something other then 5432. >> > >> > 3) If you have determined you really don't need that server and have >> all >> > the data safely backed up or moved to the other server, then(THIS IS >> NOT >> > REVERSIBLE): >> > >> > sudo pg_dropcluster 14 main_old >> >> Blaming it on not enough coffee, but I just noticed both clusters are >> pointing at the same data directory. I would NOT do: >> >> sudo pg_dropcluster 14 main_old >> >> > >> >> >> >> Thanks! >> >> >> > >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >>
Re: Two started cluster on the same data directory and port
Yes, it exists but it is obsolete. The files and directories are all dated 2022. It was obsolete and no longer valid, it could be deleted. Only the main cluster is correct. El jue, 30 nov 2023 a las 17:43, Adrian Klaver () escribió: > On 11/30/23 08:37, Loles wrote: > > Every time I stop the main_old cluster with sudo pg_ctlcluster stop 14 > > main_old, both are stopped. And when I raise sudo pg_ctlcluster start 14 > > main, both are raised. > > > > I don't know how I got into this situation and I don't know how to > > resolve it. > > Is there both?: > > /etc/postgresql/14/main/ > /etc/postgresql/14/main_old/ > > > > > > > El jue, 30 nov 2023 a las 17:34, Loles ( > <mailto:lole...@gmail.com>>) escribió: > > > > Indeed, that is the problem, they are two different clusters, > > pointing to the same data directory and reading from the same port. > > > > I can't change the port because it is the same postgresql.conf > > > > I don't know how to disassociate the main_old cluster from the data > > directory or how to resolve this situation. > > > > El jue, 30 nov 2023 a las 16:32, Adrian Klaver > > (mailto:adrian.kla...@aklaver.com>>) > > escribió: > > > > On 11/30/23 07:23, Adrian Klaver wrote: > > > On 11/30/23 05:37, Loles wrote: > > >> Hi! > > >> > > >> I have this situation on a server that is giving me problems. > > >> > > >> postgres@hostname:~$ pg_lsclusters > > >> Ver Cluster Port Status OwnerData directory > > Log file > > >> 14 main 5432 online postgres /var/lib/postgresql/14/main > > >> /var/log/postgresql/postgresql-14-main.log > > >> 14 main_old 5432 online postgres /var/lib/postgresql/14/main > > >> /var/log/postgresql/postgresql-14-main_old.log > > >> > > >> How can I stop, even delete, the main_old cluster? > > > > > > Assuming you are on Debian/Ubuntu. > > > > > > 1) sudo pg_ctlcluster stop 14 main_old > > > > > > This will stop the server for this session > > > > > > 2) sudo vi /etc/postgresql/14/main_old/start.conf > > > > > > Change auto to manual. > > > This will keep the server from restarting on reboot. > > > You can still start it using > > > sudo pg_ctlcluster start 14 main_old > > > Though if you want to do that then: > > > vi /etc/postgresql/14/main_old/postgresql.conf > > > and change port to something other then 5432. > > > > > > 3) If you have determined you really don't need that server > > and have all > > > the data safely backed up or moved to the other server, > > then(THIS IS NOT > > > REVERSIBLE): > > > > > > sudo pg_dropcluster 14 main_old > > > > Blaming it on not enough coffee, but I just noticed both > > clusters are > > pointing at the same data directory. I would NOT do: > > > > sudo pg_dropcluster 14 main_old > > > > > > > >> > > >> Thanks! > > >> > > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Two started cluster on the same data directory and port
root@hostname:~# su - postgres postgres@hostname:~$ *nano /etc/postgresql/14/main_old/start.conf* postgres@hostname:~$ exit cerrar sesión root@hostname:~# *pg_ctlcluster stop 14 main* *(change auto for disabled and exit with save)* root@hostname:~# ps -ef | grep postgres root26091512 0 18:24 pts/100:00:00 grep postgres root@hostname:~# pg_lsclusters Ver Cluster Port Status OwnerData directory Log file *14 main 5432 down* postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log *14 main_old 5432 down * postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main_old.log root@hostname:~# root@hostname:~# root@hostname:~# *pg_ctlcluster start 14 main* root@hostname:~# ps -ef | grep postgres postgres2620 1 2 18:24 ?00:00:00 /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/main -c config_file=/etc/postgresql/14/main/postgresql.conf postgres26252620 0 18:24 ?00:00:00 postgres: 14/main: checkpointer postgres26262620 0 18:24 ?00:00:00 postgres: 14/main: background writer postgres26272620 0 18:24 ?00:00:00 postgres: 14/main: walwriter postgres26282620 0 18:24 ?00:00:00 postgres: 14/main: autovacuum launcher postgres26292620 0 18:24 ?00:00:00 postgres: 14/main: stats collector postgres26302620 0 18:24 ?00:00:00 postgres: 14/main: logical replication launcher root26421512 0 18:24 pts/100:00:00 grep postgres root@hostname:~# pg_lsclusters Ver Cluster Port Status OwnerData directory Log file *14 main 5432 online* postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log *14 main_old 5432 online *postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main_old.log Start, up the two cluster. El jue, 30 nov 2023 a las 18:01, Adrian Klaver () escribió: > On 11/30/23 08:46, Loles wrote: > > Yes, it exists but it is obsolete. The files and directories are all > > dated 2022. > > > > It was obsolete and no longer valid, it could be deleted. Only the main > > cluster is correct. > > First try: > > vi /etc/postgresql/14/main_old/start.conf > > and change auto to disabled. > > Stop the server(s) and then: > > sudo pg_ctlcluster starp 14 main > > > > > El jue, 30 nov 2023 a las 17:43, Adrian Klaver > > (mailto:adrian.kla...@aklaver.com>>) > escribió: > > > > On 11/30/23 08:37, Loles wrote: > > > Every time I stop the main_old cluster with sudo pg_ctlcluster > > stop 14 > > > main_old, both are stopped. And when I raise sudo pg_ctlcluster > > start 14 > > > main, both are raised. > > > > > > I don't know how I got into this situation and I don't know how to > > > resolve it. > > > > Is there both?: > > > > /etc/postgresql/14/main/ > > /etc/postgresql/14/main_old/ > > > > > > > > > > > El jue, 30 nov 2023 a las 17:34, Loles ( > <mailto:lole...@gmail.com> > > > <mailto:lole...@gmail.com <mailto:lole...@gmail.com>>>) escribió: > > > > > > Indeed, that is the problem, they are two different clusters, > > > pointing to the same data directory and reading from the same > > port. > > > > > > I can't change the port because it is the same postgresql.conf > > > > > > I don't know how to disassociate the main_old cluster from > > the data > > > directory or how to resolve this situation. > > > > > > El jue, 30 nov 2023 a las 16:32, Adrian Klaver > > > ( > <mailto:adrian.kla...@aklaver.com> <mailto:adrian.kla...@aklaver.com > > <mailto:adrian.kla...@aklaver.com>>>) > > > escribió: > > > > > > On 11/30/23 07:23, Adrian Klaver wrote: > > > > On 11/30/23 05:37, Loles wrote: > > > >> Hi! > > > >> > > > >> I have this situation on a server that is giving me > > problems. > > > >> > > > >> postgres@hostname:~$ pg_lsclusters > > > >> Ver Cluster Port Status OwnerData directory > > > Log file > > > >> 14 main 5432 online postgres > > /var/lib/postgresql/14/main > > > >> /var/log/p
Re: Two started cluster on the same data directory and port
No, they are not symbolic links. I have thought about moving the main_old directory somewhere else and it doesn't change anything because the cluster main_old is linked to the main data directory. In fact, neither the log nor any main_old data file changes, they are all from 2020. It's a strange case El jue., 30 nov. 2023 16:23, Adrian Klaver escribió: > On 11/30/23 05:37, Loles wrote: > > Hi! > > > > I have this situation on a server that is giving me problems. > > > > postgres@hostname:~$ pg_lsclusters > > Ver Cluster Port Status OwnerData directory Log file > > 14 main 5432 online postgres /var/lib/postgresql/14/main > > /var/log/postgresql/postgresql-14-main.log > > 14 main_old 5432 online postgres /var/lib/postgresql/14/main > > /var/log/postgresql/postgresql-14-main_old.log > > > > How can I stop, even delete, the main_old cluster? > > Assuming you are on Debian/Ubuntu. > > 1) sudo pg_ctlcluster stop 14 main_old > > This will stop the server for this session > > 2) sudo vi /etc/postgresql/14/main_old/start.conf > > Change auto to manual. > This will keep the server from restarting on reboot. > You can still start it using > sudo pg_ctlcluster start 14 main_old > Though if you want to do that then: > vi /etc/postgresql/14/main_old/postgresql.conf > and change port to something other then 5432. > > 3) If you have determined you really don't need that server and have all > the data safely backed up or moved to the other server, then(THIS IS NOT > REVERSIBLE): > > sudo pg_dropcluster 14 main_old > > > > > Thanks! > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Two started cluster on the same data directory and port
Yes, it seems to be caused by systemd. I'll investigate there. If not, I can upgrade PostgreSQL and get rid of server 14. Thank you very much Adrian... I'm going to get a cup of coffee because the topic requires it :D El vie, 1 dic 2023 a las 6:19, Adrian Klaver () escribió: > On 11/30/23 20:43, Matthias Apitz wrote: > > El día jueves, noviembre 30, 2023 a las 02:10:25p. m. -0800, Adrian > Klaver escribió: > > > >> On 11/30/23 12:35, Adrian Klaver wrote: > >>> On 11/30/23 10:35, Adrian Klaver wrote: > >>>> On 11/30/23 09:27, Loles wrote: > >>>> > >> > >>> cd /etc/postgresql/14/ > >>> sudo rm -r main_old/ > >>> or > >>> sudo cp -r main_old > >> > >> Arrgh. > >> > >> sudo mv -r main_old > >> > >> Memo to self don't eat lunch and copy/paste at same time. > > > > Hmmm > > > > purism@pureos:~$ uname -s > > Linux > > purism@pureos:~$ mv -r foo bar > > mv: invalid option -- 'r' > > The gift that keeps on giving. Third time's a charm?: > > sudo mv main_old/ > > > Try 'mv --help' for more information. > > > > matthias > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
[no subject]
Hi! Let's see if someone can answer my question. In a default PostgreSQL 13 installation, starting the server starts a process called "logical replication launcher". I know what logical replication is but I don't know what this process does on the server. Even if you change the default setting of wal_level to minimal, the process continues to start after the server is restarted so that it takes the change in the wal_level parameter. ¿? Thanks to all!
Re:
Why the Server startup this background process? Isn't a mandatory process. And what's the logical replicación Launcher do? El dom., 16 may. 2021 21:21, Ron escribió: > On 5/16/21 1:47 PM, Loles wrote: > > Hi! > > > > Let's see if someone can answer my question. > > > > In a default PostgreSQL 13 installation, starting the server starts a > > process called "logical replication launcher". > > > > I know what logical replication is but I don't know what this process > does > > on the server. > > > > Even if you change the default setting of wal_level to minimal, the > > process continues to start after the server is restarted so that it > takes > > the change in the wal_level parameter. > > > > ¿? > > What's your question? > > -- > Angular momentum makes the world go 'round. > > >
Re:
I don't understand why the server starts that process if I'm not using replication of any kind. The server starts it with the default configuration of version 13. I think that it consumes resources that I do not need because, as I have commented, I will not use replication of any kind. El lun, 17 may 2021 a las 7:41, Dilip Kumar () escribió: > On Mon, May 17, 2021 at 12:18 AM Loles wrote: > > > > Hi! > > > > Let's see if someone can answer my question. > > > > In a default PostgreSQL 13 installation, starting the server starts a > process called "logical replication launcher". > > > > I know what logical replication is but I don't know what this process > does on the server. > > > > Even if you change the default setting of wal_level to minimal, the > process continues to start after the server is restarted so that it takes > the change in the wal_level parameter. > > This process launches the worker process for each subscription for > applying the logical replication changes. It does not matter what is > the wal level, because wal level should be logical to create a > publisher but this is used for launching the worker for the > subscriber. So in short this process will scan the subscriber and > launches the apply worker if there is no worker corresponding to some > subscription. > > -- > Regards, > Dilip Kumar > EnterpriseDB: http://www.enterprisedb.com >
Re:
Ok, now I know. Thank you very much to all!! El lun, 17 may 2021 a las 8:37, David G. Johnston (< david.g.johns...@gmail.com>) escribió: > On Sunday, May 16, 2021, Loles wrote: > >> I don't understand why the server starts that process if I'm not using >> replication of any kind. >> >> >> The server starts it with the default configuration of version 13. >> >> I think that it consumes resources that I do not need because, as I have >> commented, I will not use replication of any kind. >> > > It starts the process because at any time someone can execute a “create > subscription” command. If no one does so the amount of time spent > confirming the subscriptions table is empty is trivial. > > So, yes, it consumes resources. A minimal amount, likely not enough to > spend time figuring out how to avoid and risking a change to the basic > operational mode of the subsystem. > > David J. > >
