Re: postgresql version 13 repo question
Yi Sun 于2021年6月30日周三 下午2:33写道： > Hello, > > As our env os version is different, some is centos 7.4, some is 7.5 and > 7.6 ..., and there is only one company repo, as I compare the packages size > and date, seems same, If we can just use 7.6 packages please? > > https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7.4-x86_64/ > https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7.5-x86_64/ > https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7.6-x86_64/ > > Thank you > Hi guys Seems they are same, so If the packages in version 7 are same can use any one please?
Re: Doubt on pgbouncer
Thank you for the clarification. On Sat, 3 Jul, 2021, 23:20 Ben Chobot, wrote: > Rama Krishnan wrote on 7/3/21 8:35 AM: > > > Hi Team, > > How can I split read and write queries using pgbouncer > > > You do it with your application. Make a pgbouncer database for read/write > queries and point it at your postgresql primary, and then make a second > pgbouncer database for read-only queries and point it at a replica. Have > your application connect to the proper database, depending upon its needs. > > It's not transparent, but it is effective. >
Re: Damaged (during upgrade?) table, how to repair?
On 7/4/21 12:29 PM, W.P. wrote: >> So you have backup of the failed machine's disk stored somewhere else? To be clear the 1TB disk is working where and with what OS? 1TB DISC is connected to OrangePi4 (as I have copied disc "sector by sector" from 500GB to 1TB, some months ago, I can't connect both of them to one machine -> same UUIDs, LVM does crazy). And then I copy single files using rsync. So you have been keeping the 500GB and 1TB disks in sync since the original copy was done? For future reference you can could have gone from 9.5 -> 11 without the intermediate upgrades. That is not true for Fedora OS Tested both ways, only "step by step" looks working (but is time consuming). Also where were you installing Postgres from the Fedora or Postgres repos? From Fedora's, during OS upgrade. pg_upgrade can skip versions, so what you saw was Fedora just doing pg_upgrade as it upgraded its version of Postgres as it upgraded its own version. One work around would have been, if possible, to install a version of Postgres 11 on another machine, the use the pg_dump from that version to dump your 9.5 instance to a safe location. Then uninstall Postgres from the Fedora 24 and do the OS upgrade. Then reinstall Postgres which would be version 11 and restore the dump file to the database. At 9.5 "era" there ware single directories (/bin /lib etc) for single machine, now as I can see, there are "single" but one for each PG version installed, with dispatcher(s) located in PATH (/usr/(local)/bin. So I want to replicate this doing my PG upgrade. From your previous post: > - booted up into single user, bring up Ethernet, now CAN start Postgres but only using pg_ctl directly, does NOT work using systemctl... So problem is (possibly) with systemd. > > Dumped base, pg_dump worked fine, dump gzipped is < 600MB, so I assume that somehow Postgres recovered from my (stupid) move... > Dumped from what Postgres instance 9.5 or 11? > 9.5, the only one on 1TB. At this point I am thoroughly confused as to where you are working the OrangePi4 or the laptop? In any case I don't see you getting a 9.5 version on the laptop in the package directories. Pretty sure the Fedora 30 repos will not have 9.5 and the Postgres repos don't go back to Fedora 30. So if you want a 9.5 instance you will need to build it from source in order to get a server that works long enough to restore the 9.5 dump to so you can then use the 11 instance pg_dump to dump in order to move to the 11 instance. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Damaged (during upgrade?) table, how to repair?
W dniu 04.07.2021 o 21:10, Adrian Klaver pisze: On 7/4/21 11:59 AM, W.P. wrote: W dniu 04.07.2021 o 19:48, Adrian Klaver pisze: On 7/4/21 9:33 AM, W.P. wrote: W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: On 7/2/21 10:18 AM, W.P. wrote: W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: So you have backup of the failed machine's disk stored somewhere else? No, I have disc from this machine, looks not damaged (random files). Only problem that OS does not boot beyond "emergency mode". I would say your second sentence contradicts your first. Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then copied "sector by sector" (and resized partitions, volumes, fs) to 1TB one. This was my "working" disc. Just dawned on me, why aren't you working directly from the 1TB disk? It has the presumably intact files from before the OS/Postgres upgrades and the power experiment. "Only problem that OS does not boot beyond "emergency mode"."... I thought the 1TB disk was copied over before you did any of the upgrades and experimentation? But I made some progress: - booted up into single user, bring up Ethernet, now CAN start Postgres but only using pg_ctl directly, does NOT work using systemctl... So problem is (possibly) with systemd. Dumped base, pg_dump worked fine, dump gzipped is < 600MB, so I assume that somehow Postgres recovered from my (stupid) move... Dumped from what Postgres instance 9.5 or 11? 9.5, the only one on 1TB. BTW, pls respond only to list. Afraid that will be hard to achieve as I my muscle memory is hard wired to hit Reply All. If you want to eliminate duplicate copies got to here: https://lists.postgresql.org/manage/ and check: Don't receive an extra copy of mails when listed in To or CC fields and then: Save THX :) Laurent
Re: Damaged (during upgrade?) table, how to repair?
>> So you have backup of the failed machine's disk stored somewhere else? No, I have disc from this machine, looks not damaged (random files). Only problem that OS does not boot beyond "emergency mode". I would say your second sentence contradicts your first. Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then copied "sector by sector" (and resized partitions, volumes, fs) to 1TB one. This was my "working" disc. To be clear the 1TB disk is working where and with what OS? 1TB DISC is connected to OrangePi4 (as I have copied disc "sector by sector" from 500GB to 1TB, some months ago, I can't connect both of them to one machine -> same UUIDs, LVM does crazy). And then I copy single files using rsync. For test, now I have put it (500GB) in another laptop, then upgraded F24 to F30 (step by step, once I had to do manually "pg_upgrade", ARAIR this was at PG10->PG11 step). For future reference you can could have gone from 9.5 -> 11 without the intermediate upgrades. That is not true for Fedora OS Tested both ways, only "step by step" looks working (but is time consuming). Also where were you installing Postgres from the Fedora or Postgres repos? From Fedora's, during OS upgrade. Now I did my crazy move, removed battery and power while "suspend to RAM". OS (F24! @ 1TB) don't start anymore (beyond "emergency"). I will reserve comment on that move. Still in future it would be prudent to do a pg_dump and stash the backup somewhere off machine before you do that sort of experiment. I use now this upgraded disc (500GB), copying files for daily work from 1TB one attached to OPi4. What I understand from the above is you are copying files from the 1TB disk to the 500GB disc to work with on the 500GB disk/machine. Am I correct? Yes. Will try, if I can achieve PG9.5 run on that "non working" one. If so, and dumpall is OK, then files on this are mainly intact (except for NetworkManager)... In that case you will be using 9.5 pg_dump(all) to do the dump, Assuming it works it is not guaranteed that it will restore forward to Postgres 11. This will require installing development packages on your new machine. Ok, should be easy :) But what PREFIX should I use during configure step? "/usr/lib/postgresql/"? And what about location of config files? The default for a source compile install is /usr/local/pgsql(https://www.postgresql.org/docs/11/install-procedure.html). I would stick with that if for no other reason then helping identifying your package and source clusters. At 9.5 "era" there ware single directories (/bin /lib etc) for single machine, now as I can see, there are "single" but one for each PG version installed, with dispatcher(s) located in PATH (/usr/(local)/bin. So I want to replicate this doing my PG upgrade. (I can see PG10 somewhere in /usr/pgsql/postgresql-10/ - bin, lib, share, postgresql-9.6 is empty). Where do you see the above, old machine or new machine? 500GB disc, after system upgrade. So those where the intermediate updates as you hopped through the OS/Postgres versions. Laurent
Re: Damaged (during upgrade?) table, how to repair?
On 7/4/21 11:59 AM, W.P. wrote: W dniu 04.07.2021 o 19:48, Adrian Klaver pisze: On 7/4/21 9:33 AM, W.P. wrote: W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: On 7/2/21 10:18 AM, W.P. wrote: W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: So you have backup of the failed machine's disk stored somewhere else? No, I have disc from this machine, looks not damaged (random files). Only problem that OS does not boot beyond "emergency mode". I would say your second sentence contradicts your first. Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then copied "sector by sector" (and resized partitions, volumes, fs) to 1TB one. This was my "working" disc. Just dawned on me, why aren't you working directly from the 1TB disk? It has the presumably intact files from before the OS/Postgres upgrades and the power experiment. "Only problem that OS does not boot beyond "emergency mode"."... I thought the 1TB disk was copied over before you did any of the upgrades and experimentation? But I made some progress: - booted up into single user, bring up Ethernet, now CAN start Postgres but only using pg_ctl directly, does NOT work using systemctl... So problem is (possibly) with systemd. Dumped base, pg_dump worked fine, dump gzipped is < 600MB, so I assume that somehow Postgres recovered from my (stupid) move... Dumped from what Postgres instance 9.5 or 11? BTW, pls respond only to list. Afraid that will be hard to achieve as I my muscle memory is hard wired to hit Reply All. If you want to eliminate duplicate copies got to here: https://lists.postgresql.org/manage/ and check: Don't receive an extra copy of mails when listed in To or CC fields and then: Save Laurent -- Adrian Klaver adrian.kla...@aklaver.com
Re: Damaged (during upgrade?) table, how to repair?
W dniu 04.07.2021 o 19:48, Adrian Klaver pisze: On 7/4/21 9:33 AM, W.P. wrote: W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: On 7/2/21 10:18 AM, W.P. wrote: W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: So you have backup of the failed machine's disk stored somewhere else? No, I have disc from this machine, looks not damaged (random files). Only problem that OS does not boot beyond "emergency mode". I would say your second sentence contradicts your first. Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then copied "sector by sector" (and resized partitions, volumes, fs) to 1TB one. This was my "working" disc. Just dawned on me, why aren't you working directly from the 1TB disk? It has the presumably intact files from before the OS/Postgres upgrades and the power experiment. "Only problem that OS does not boot beyond "emergency mode"."... But I made some progress: - booted up into single user, bring up Ethernet, now CAN start Postgres but only using pg_ctl directly, does NOT work using systemctl... So problem is (possibly) with systemd. Dumped base, pg_dump worked fine, dump gzipped is < 600MB, so I assume that somehow Postgres recovered from my (stupid) move... BTW, pls respond only to list. Laurent
Re: Damaged (during upgrade?) table, how to repair?
On 7/4/21 9:33 AM, W.P. wrote: W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: On 7/2/21 10:18 AM, W.P. wrote: W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: So you have backup of the failed machine's disk stored somewhere else? No, I have disc from this machine, looks not damaged (random files). Only problem that OS does not boot beyond "emergency mode". I would say your second sentence contradicts your first. Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then copied "sector by sector" (and resized partitions, volumes, fs) to 1TB one. This was my "working" disc. Just dawned on me, why aren't you working directly from the 1TB disk? It has the presumably intact files from before the OS/Postgres upgrades and the power experiment. Laurent -- Adrian Klaver adrian.kla...@aklaver.com
Re: Damaged (during upgrade?) table, how to repair?
On 7/4/21 9:33 AM, W.P. wrote: W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: On 7/2/21 10:18 AM, W.P. wrote: W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: So you have backup of the failed machine's disk stored somewhere else? No, I have disc from this machine, looks not damaged (random files). Only problem that OS does not boot beyond "emergency mode". I would say your second sentence contradicts your first. Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then copied "sector by sector" (and resized partitions, volumes, fs) to 1TB one. This was my "working" disc. To be clear the 1TB disk is working where and with what OS? For test, now I have put it (500GB) in another laptop, then upgraded F24 to F30 (step by step, once I had to do manually "pg_upgrade", ARAIR this was at PG10->PG11 step). For future reference you can could have gone from 9.5 -> 11 without the intermediate upgrades. Also where were you installing Postgres from the Fedora or Postgres repos? Now I did my crazy move, removed battery and power while "suspend to RAM". OS (F24! @ 1TB) don't start anymore (beyond "emergency"). I will reserve comment on that move. Still in future it would be prudent to do a pg_dump and stash the backup somewhere off machine before you do that sort of experiment. I use now this upgraded disc (500GB), copying files for daily work from 1TB one attached to OPi4. What I understand from the above is you are copying files from the 1TB disk to the 500GB disc to work with on the 500GB disk/machine. Am I correct? Will try, if I can achieve PG9.5 run on that "non working" one. If so, and dumpall is OK, then files on this are mainly intact (except for NetworkManager)... In that case you will be using 9.5 pg_dump(all) to do the dump, Assuming it works it is not guaranteed that it will restore forward to Postgres 11. This will require installing development packages on your new machine. Ok, should be easy :) But what PREFIX should I use during configure step? "/usr/lib/postgresql/"? And what about location of config files? The default for a source compile install is /usr/local/pgsql(https://www.postgresql.org/docs/11/install-procedure.html). I would stick with that if for no other reason then helping identifying your package and source clusters. (I can see PG10 somewhere in /usr/pgsql/postgresql-10/ - bin, lib, share, postgresql-9.6 is empty). Where do you see the above, old machine or new machine? 500GB disc, after system upgrade. So those where the intermediate updates as you hopped through the OS/Postgres versions. Laurent -- Adrian Klaver adrian.kla...@aklaver.com
Re: Damaged (during upgrade?) table, how to repair?
W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: On 7/2/21 10:18 AM, W.P. wrote: W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: So you have backup of the failed machine's disk stored somewhere else? No, I have disc from this machine, looks not damaged (random files). Only problem that OS does not boot beyond "emergency mode". I would say your second sentence contradicts your first. Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then copied "sector by sector" (and resized partitions, volumes, fs) to 1TB one. This was my "working" disc. For test, now I have put it (500GB) in another laptop, then upgraded F24 to F30 (step by step, once I had to do manually "pg_upgrade", ARAIR this was at PG10->PG11 step). Now I did my crazy move, removed battery and power while "suspend to RAM". OS (F24! @ 1TB) don't start anymore (beyond "emergency"). I use now this upgraded disc (500GB), copying files for daily work from 1TB one attached to OPi4. Will try, if I can achieve PG9.5 run on that "non working" one. If so, and dumpall is OK, then files on this are mainly intact (except for NetworkManager)... In any case, we got to this point as the upgrade(more information on how that was done would be nice) from the 9.5 instance to 11.12 failed. Given that there is good chance that was due to corruption of files in the Postgres cluster on the above disc, it is very probable that going back to that disk will repeat the problem. Still see more below. I did a "step-by-step" OS upgrade using dnf, and (once) manual pg_upgrade (when there was no "automatic" upgrade, db failed to start). Otherwise how are you going to get the 9.5 instance to the new machine? In modern Postgres I've seen there is possibility to have several (different versions) "clusters" on same machine. My question (for now) is how to do it? I'm going to say the easiest way to do this on the new machine would be to build 9.5 from source: https://www.postgresql.org/docs/9.5/installation.html https://www.postgresql.org/ftp/source/v9.5.25/ This will require installing development packages on your new machine. Ok, should be easy :) But what PREFIX should I use during configure step? "/usr/lib/postgresql/"? And what about location of config files? (I can see PG10 somewhere in /usr/pgsql/postgresql-10/ - bin, lib, share, postgresql-9.6 is empty). Where do you see the above, old machine or new machine? 500GB disc, after system upgrade. Laurent
Re: Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes
On Sun, 4 Jul 2021 at 15:53, Avi Weinberg wrote: > I'm using logical replication to copy data from multiple tables to a > single destination table. At times the structure of the source table needs > to change. However, not all source table will have their structure updated > at the same time. Let's assume, for example, a column type needs to be > changed (but solution needs to work for column addition, deletion, rename > etc.). What is the preferable approach: > > > >1. To add another column to the destination table where its type will >be the new type. Source tables, that have the new column type, will write >to the new column. While source tables with old column type will write to >the old column. A view will do the proper casting and will show a single >column to user. >2. Add a new table (versioned: table_name_v2) where source tables that >have a new structure will write to the new destination table, while old >source tables will write to the old destination table. A view with UNION >and casting will combine all tables. >3. A better way? > > Does the below not work for all alter table changes on publisher. I have been testing on a demo setup pg14beta, and subscribers are able to catch up fine. on publisher (port 5001) postgres=# alter table t alter COLUMN name type bigint using name::bigint; on subscriber (port 5002) postgres=# alter table t alter COLUMN name type bigint using name::bigint; ALTER TABLE postgres=# alter subscription mysub refresh publication; this should work for all the cases for ddl changes right. -- demo -- create table on pub and sub postgres@db:~/playground/logical_replication$ psql -p 5001 psql (14beta1) Type "help" for help. postgres=# create table t(id int primary key, name text); CREATE TABLE postgres=# \q postgres@db:~/playground/logical_replication$ psql -p 5002 psql (14beta1) Type "help" for help. postgres=# create table t(id int primary key, name text); CREATE TABLE postgres=# \q -- insert dummy data to check sub rx changes postgres@db:~/playground/logical_replication$ psql -p 5001 psql (14beta1) Type "help" for help. postgres=# insert into t values (1, 1::text); INSERT 0 1 postgres=# \q postgres@db:~/playground/logical_replication$ psql -p 5002 psql (14beta1) Type "help" for help. postgres=# table t; id | name +-- (0 rows) postgres=# alter subscription mysub refresh publication; -- this is because i dropped table with publication enabled ALTER SUBSCRIPTION postgres=# table t; id | name +-- 1 | 1 (1 row) postgres=# \q -- alter table alter column change type on pub postgres@db:~/playground/logical_replication$ psql -p 5001 psql (14beta1) Type "help" for help. postgres=# alter table t alter COLUMN name type bigint using name::bigint; ALTER TABLE postgres=# \q -- alter table alter column change type on sub postgres@db:~/playground/logical_replication$ psql -p 5002 psql (14beta1) Type "help" for help. postgres=# alter table t alter COLUMN name type bigint using name::bigint; ALTER TABLE postgres=# \q -- insert new data based on new column type postgres@db:~/playground/logical_replication$ psql -p 5001 psql (14beta1) Type "help" for help. postgres=# insert into t values (2, 100); INSERT 0 1 postgres=# \q -- check new data on sub postgres@db:~/playground/logical_replication$ psql -p 5002 psql (14beta1) Type "help" for help. postgres=# table t; id | name +- 1 | 1 2 | 100 (2 rows) postgres=# \q --alter table alter col type on pub and insert data postgres@db:~/playground/logical_replication$ psql -p 5001 psql (14beta1) Type "help" for help. postgres=# alter table t alter COLUMN name type text using name::text; ALTER TABLE postgres=# insert into t values (3, 'three'); INSERT 0 1 postgres=# \q --alter table alter col type on sub, changes will not come till refresh pub postgres@db:~/playground/logical_replication$ psql -p 5002 psql (14beta1) Type "help" for help. postgres=# table t; id | name +- 1 | 1 2 | 100 (2 rows) postgres=# alter table t alter COLUMN name type text using name::text; ALTER TABLE postgres=# table t; id | name +- 1 | 1 2 | 100 (2 rows) postgres=# alter subscription mysub refresh publication; -- refresh would get back changes ALTER SUBSCRIPTION postgres=# table t; id | name +- 1 | 1 2 | 100 3 | three (3 rows) or have I misunderstood the question? -- Thanks, Vijay Mumbai, India
Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes
I'm using logical replication to copy data from multiple tables to a single destination table. At times the structure of the source table needs to change. However, not all source table will have their structure updated at the same time. Let's assume, for example, a column type needs to be changed (but solution needs to work for column addition, deletion, rename etc.). What is the preferable approach: 1. To add another column to the destination table where its type will be the new type. Source tables, that have the new column type, will write to the new column. While source tables with old column type will write to the old column. A view will do the proper casting and will show a single column to user. 2. Add a new table (versioned: table_name_v2) where source tables that have a new structure will write to the new destination table, while old source tables will write to the old destination table. A view with UNION and casting will combine all tables. 3. A better way? Thanks IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
