Re: logging proxy
On Fri, Nov 1, 2019 at 01:58:10AM +0300, Олег Самойлов wrote: > Does anyone know PostgresQL proxy which can log queries with username, > ip and affected rows for security reason. PostgresQL itself can log > almost all, except affected rows. You can use the server logs to get the non-row information, then use streaming replication with logical decoding to get the rows for each transaction started by the user. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: type SERIAL in C host-struct
El día jueves, noviembre 07, 2019 a las 12:39:39p. m. +0100, Matthias Apitz
escribió:
>
> Hello,
>
> We're struggling with the following problem (here show in a simplified
> case).
>
> We have in a PG 11.4 database a table with two columns: SERIAL, VARCHAR(11).
>
> In the ESQL/C pgm the code is:
>
> EXEC SQL BEGIN DECLARE SECTION;
> ...
> struct {
> int ser;
> char name [11];
> } host_struct;
> EXEC SQL END DECLARE SECTION;
>
> an INSERT with
>
> strcpy(host_struct.name, "Sigrid");
> host_struct.ser = 0;
>
> EXEC SQL INSERT INTO lina VALUES (:host_struct);
>
> works but, sets the SERIAL column to 0;
>
> an INSERT with
>
> EXEC SQL INSERT INTO lina VALUES (DEFAULT, :host_struct.name);
>
> works correctly and increments the SERIAL on every INSERT:
At the end of the day we came up with the following solution:
strcpy(host_struct.name, "Sigrid");
EXEC SQL select nextval('lina_lid_seq') into :host_struct.ser;
EXEC SQL INSERT INTO lina VALUES ( :host_struct );
which seems to work fine. Any comments about side effects?
The layout of the table 'lina' is ( serial lid, varchar name )
Thanks,
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen
durchschaut"
"Believe little, scrutinise all, think by your own: How see through
manipulations"
ISBN-10: 386489218X
signature.asc
Description: PGP signature
Re: AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth
"Zwettler Markus (OIZ)" wrote on 2019/11/07 11:32:42 AM: > From: "Zwettler Markus (OIZ)" > To: Adrian Klaver , "pgsql- > gene...@lists.postgresql.org" > Date: 2019/11/07 11:33 AM > Subject: [EXTERNAL] AW: AW: AW: broken backup trail in case of > quickly patroni switchback and forth > > 3) > Patroni does only failovers. Also in case of regular shutdown of the > primary. A failover is a promote of the standby + automatic > reinstate (pg_rewind or pg_basebackup) of the former primary. This is not accurate. Patroni does controlled switchovers as well as failovers. Controlled switchover issues a fast shutdown to Postgres, hard ones issue an immediate shutdown. From this point, it's how Postgres responds to those that matter. Fast shutdown will attempt to ensure the wal stream is transmitted to the replica and the wal files are archived. Immediate shutdown will not do any of this. This issue explains more about when Patroni may choose an immediate shutdown (it might not be totally accurate anymore as it's a year old). https://github.com/zalando/patroni/issues/837#issuecomment-433686687 I agree with the Patroni folks that this is not a Patroni issue, but simply how Postgres responds to the required shutdown types.
Re: broken backup trail in case of quickly patroni switchback and forth
On Thu, 2019-11-07 at 13:52 +, Zwettler Markus (OIZ) wrote: > we are using Patroni for management of our Postgres standby databases. > > we take our (wal) backups on the primary side based on intervals and > thresholds. > our archived wal's are written to a local wal directory first and moved to > tape afterwards. > > we got a case where Patroni switched back and forth sides quickly, e.g.: > 12:00h: primary - standby > 12:05h: standby - primary > 12:10h: primary - standby > > we realised that we will not have a wal backup of those wal's generated > between 12:05h and 12:10h in this scenario. > > how can we make sure that the whole wal sequence trail will be backuped? any > idea? You'll have to archive WAL from both machines. Then you have everything you should need. Make sure "recovery_target_timeline = 'latest'" so that recovery will follow the timeline jumps. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth
3) Patroni does only failovers. Also in case of regular shutdown of the primary. A failover is a promote of the standby + automatic reinstate (pg_rewind or pg_basebackup) of the former primary. Time: role site 1 - role site 2 12:00h: primary - standby => Some clients commited some transactions; Primary stopped => Failover to standby 12:05h: standby - primary => Some clients connected + commited some transactions; Primary stopped => Failover to standby 12:10h: primary - standby Patroni.yml) $ cat pcl_l702.yml scope: pcl_l702 name: pcl_l702@tstm49003 namespace: /patroni/ log: level: DEBUG dir: /opt/app/patroni/etc/log/ file_num: 10 file_size: 104857600 restapi: listen: tstm49003.tstglobal.tst.loc:8010 connect_address: tstm49003.tstglobal.tst.loc:8010 etcd: hosts: etcdlab01.tstglobal.tst.loc:2379,etcdlab02.tstglobal.tst.loc:2379,etcdlab03.tstglobal.tst.loc:2379,etcdlab04.tstglobal.tst.loc:2379,etcdlab05.tstglobal.tst.loc:2379 username: patroni password: censored bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 master_start_timeout: 300 synchronous_mode: true postgresql: use_pg_rewind: true use_slots: true # NO BOOTSTRAPPING USED method: do_not_bootstrap do_not_bootstrap: command: /bin/false postgresql: authentication: replication: username: repadmin password: censored superuser: username: patroni password: censored callbacks: on_reload: /opt/app/patroni/etc/callback_patroni.sh on_restart: /opt/app/patroni/etc/callback_patroni.sh on_role_change: /opt/app/patroni/etc/callback_patroni.sh on_start: /opt/app/patroni/etc/callback_patroni.sh on_stop: /opt/app/patroni/etc/callback_patroni.sh connect_address: tstm49003.tstglobal.tst.loc:5436 database: pcl_l702 data_dir: /pgdata/pcl_l702 bin_dir: /usr/pgsql-9.6/bin listen: localhost,tstm49003.tstglobal.tst.loc,pcl_l702.tstglobal.tst.loc:5436 pgpass: /home/postgres/.pgpass_patroni recovery_conf: restore_command: cp /pgxlog_archive/pcl_l702/%f %p parameters: hot_standby_feedback: on wal_keep_segments: 64 use_pg_rewind: true watchdog: mode: automatic device: /dev/watchdog safety_margin: 5 tags: nofailover: false noloadbalance: false clonefrom: false nosync: false -Ursprüngliche Nachricht- Von: Adrian Klaver Gesendet: Donnerstag, 7. November 2019 17:06 An: Zwettler Markus (OIZ) ; pgsql-general@lists.postgresql.org Betreff: Re: AW: AW: broken backup trail in case of quickly patroni switchback and forth On 11/7/19 7:47 AM, Zwettler Markus (OIZ) wrote: I am heading out the door so I will not have time to look at below until later. For those that get a chance before then, it would be nice to have the Patroni conf file information also. The Patroni information may answer the question, but it case it does not what actually is failover in 3) below? > 1) 9.6 > > > > 2) > $ cat postgresql.conf > # Do not edit this file manually! > # It will be overwritten by Patroni! > include 'postgresql.base.conf' > > cluster_name = 'pcl_l702' > hot_standby = 'on' > hot_standby_feedback = 'True' > listen_addresses = > 'localhost,tstm49003.tstglobal.tst.loc,pcl_l702.tstglobal.tst.loc' > max_connections = '100' > max_locks_per_transaction = '64' > max_prepared_transactions = '0' > max_replication_slots = '10' > max_wal_senders = '10' > max_worker_processes = '8' > port = '5436' > track_commit_timestamp = 'off' > wal_keep_segments = '8' > wal_level = 'replica' > wal_log_hints = 'on' > hba_file = '/pgdata/pcl_l702/pg_hba.conf' > ident_file = '/pgdata/pcl_l702/pg_ident.conf' > $ > $ > $ > $ cat postgresql.base.conf > datestyle = 'iso, mdy' > default_text_search_config = 'pg_catalog.english' > dynamic_shared_memory_type = posix > lc_messages = 'en_US.UTF-8' > lc_monetary = 'de_CH.UTF-8' > lc_numeric = 'de_CH.UTF-8' > lc_time = 'de_CH.UTF-8' > logging_collector = on > log_directory = 'pg_log' > log_rotation_age = 1d > log_rotation_size = 0 > log_timezone = 'Europe/Vaduz' > log_truncate_on_rotation = on > max_connections = 100 > timezone = 'Europe/Vaduz' > archive_command = 'test ! -f /tmp/pg_archive_backup_running_on_pcl_l702* && > rsync --checksum %p /pgxlog_archive/pcl_l702/%f' > archive_mode = on > archive_timeout = 1800 > cluster_name = pcl_l702 > cron.database_name = 'pdb_l72_oiz' > # effective_cache_size > listen_addresses = '*' > log_connections = on > log_destination = 'stderr, csvlog' > log_disconnections = on > log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' > log_line_prefix = '%t : %h=>%u@%d : %p-%c-%v : %e ' > log_statement = 'ddl' > max_wal_senders = 5 > port = 5436 > shared_buffers = 512MB > shared_preload_libraries = 'auto_explain, pg_stat_statements, pg_cron, > pg_statsinfo' > wal_buffers = 16MB > wal_compression = on > wal_level = replica > # work_mem > > > > 3) > 12:00h: primary - standby > => Some clients
Re: 11 -> 12 upgrade on Debian Ubuntu
On 2019-11-07 10:59:37 -0500, stan wrote: > On Thu, Nov 07, 2019 at 07:52:14AM -0800, Adrian Klaver wrote: > > On 11/7/19 7:45 AM, stan wrote: > > > I am in the middle of a project, and it looks like version 12 is now what > > > the Debian/Ubuntu package managers want to update to. > > > > This should be a dist-upgrade correct? > > Correct. > > > On a my Ubuntu instance that just installed the Postgres 12 version and > > started it(also auto start in start.conf). This was just the template > > databases and postgres db. No data was moved over from the 11 instance that > > is running. > > > Thanks, that is helpful to know. To migrate the data to the new database, run pg_upgradecluster. This is a wrapper around pg_upgrade which knows about details of a standard Debian/Ubuntu PostgreSQL installation (like directory layout, etc.), so it should be simpler and safer than invoking pg_upgrade yourself (and pg_upgrade is hidden in /usr/lib/postgresql/*/bin to prevent you from invoking it accidentally). hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: AW: AW: broken backup trail in case of quickly patroni switchback and forth
On 11/7/19 7:47 AM, Zwettler Markus (OIZ) wrote: I am heading out the door so I will not have time to look at below until later. For those that get a chance before then, it would be nice to have the Patroni conf file information also. The Patroni information may answer the question, but it case it does not what actually is failover in 3) below? 1) 9.6 2) $ cat postgresql.conf # Do not edit this file manually! # It will be overwritten by Patroni! include 'postgresql.base.conf' cluster_name = 'pcl_l702' hot_standby = 'on' hot_standby_feedback = 'True' listen_addresses = 'localhost,tstm49003.tstglobal.tst.loc,pcl_l702.tstglobal.tst.loc' max_connections = '100' max_locks_per_transaction = '64' max_prepared_transactions = '0' max_replication_slots = '10' max_wal_senders = '10' max_worker_processes = '8' port = '5436' track_commit_timestamp = 'off' wal_keep_segments = '8' wal_level = 'replica' wal_log_hints = 'on' hba_file = '/pgdata/pcl_l702/pg_hba.conf' ident_file = '/pgdata/pcl_l702/pg_ident.conf' $ $ $ $ cat postgresql.base.conf datestyle = 'iso, mdy' default_text_search_config = 'pg_catalog.english' dynamic_shared_memory_type = posix lc_messages = 'en_US.UTF-8' lc_monetary = 'de_CH.UTF-8' lc_numeric = 'de_CH.UTF-8' lc_time = 'de_CH.UTF-8' logging_collector = on log_directory = 'pg_log' log_rotation_age = 1d log_rotation_size = 0 log_timezone = 'Europe/Vaduz' log_truncate_on_rotation = on max_connections = 100 timezone = 'Europe/Vaduz' archive_command = 'test ! -f /tmp/pg_archive_backup_running_on_pcl_l702* && rsync --checksum %p /pgxlog_archive/pcl_l702/%f' archive_mode = on archive_timeout = 1800 cluster_name = pcl_l702 cron.database_name = 'pdb_l72_oiz' # effective_cache_size listen_addresses = '*' log_connections = on log_destination = 'stderr, csvlog' log_disconnections = on log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_line_prefix = '%t : %h=>%u@%d : %p-%c-%v : %e ' log_statement = 'ddl' max_wal_senders = 5 port = 5436 shared_buffers = 512MB shared_preload_libraries = 'auto_explain, pg_stat_statements, pg_cron, pg_statsinfo' wal_buffers = 16MB wal_compression = on wal_level = replica # work_mem 3) 12:00h: primary - standby => Some clients commited some transactions; Failover 12:05h: standby - primary => Some clients connected + commited some transactions; Failover 12:10h: primary - standby On 11/7/19 7:18 AM, Zwettler Markus (OIZ) wrote: I already asked the Patroni folks. They told me this is not related to Patroni but Postgresql. ;-) Hard to say without more information: 1) Postgres version 2) Setup/config info 3) Detail if what happened between 12:00 and 12:10 - Markus On 11/7/19 5:52 AM, Zwettler Markus (OIZ) wrote: we are using Patroni for management of our Postgres standby databases. we take our (wal) backups on the primary side based on intervals and thresholds. our archived wal's are written to a local wal directory first and moved to tape afterwards. we got a case where Patroni switched back and forth sides quickly, e.g.: 12:00h: primary - standby 12:05h: standby - primary 12:10h: primary - standby we realised that we will not have a wal backup of those wal's generated between 12:05h and 12:10h in this scenario. how can we make sure that the whole wal sequence trail will be backuped? any idea? Probably best to ask the Patroni folks: https://github.com/zalando/patroni#community - Markus -- Adrian Klaver adrian.kla...@aklaver.com
Re: 11 -> 12 upgrade on Debian Ubuntu
On Thu, Nov 07, 2019 at 07:52:14AM -0800, Adrian Klaver wrote: > On 11/7/19 7:45 AM, stan wrote: > > I am in the middle of a project, and it looks like version 12 is now what > > the Debian/Ubuntu package managers want to update to. > > This should be a dist-upgrade correct? Correct. > > On a my Ubuntu instance that just installed the Postgres 12 version and > started it(also auto start in start.conf). This was just the template > databases and postgres db. No data was moved over from the 11 instance that > is running. > Thanks, that is helpful to know. > > -- > Adrian Klaver > adrian.kla...@aklaver.com -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: 11 -> 12 upgrade on Debian Ubuntu
On 11/7/19 7:45 AM, stan wrote: I am in the middle of a project, and it looks like version 12 is now what the Debian/Ubuntu package managers want to update to. This should be a dist-upgrade correct? On a my Ubuntu instance that just installed the Postgres 12 version and started it(also auto start in start.conf). This was just the template databases and postgres db. No data was moved over from the 11 instance that is running. I of course, will do this first on a test machine, not the "production", or "develop,met" machines, but I thought i would solicit the group wisdom on this. Are there any things I should watch out for here? Will my data be preserved during this upgrade? We are just beginning to put real data in the "production" instance, and just yesterday, I set up a script to do backups using pg_basebackup. BTW this is the 1st time I have sued this, having used pg_dump in the past. Database is fairly small with just one tablespace if that matters. Thanks for anyone's input. -- Adrian Klaver adrian.kla...@aklaver.com
AW: AW: broken backup trail in case of quickly patroni switchback and forth
1) 9.6 2) $ cat postgresql.conf # Do not edit this file manually! # It will be overwritten by Patroni! include 'postgresql.base.conf' cluster_name = 'pcl_l702' hot_standby = 'on' hot_standby_feedback = 'True' listen_addresses = 'localhost,tstm49003.tstglobal.tst.loc,pcl_l702.tstglobal.tst.loc' max_connections = '100' max_locks_per_transaction = '64' max_prepared_transactions = '0' max_replication_slots = '10' max_wal_senders = '10' max_worker_processes = '8' port = '5436' track_commit_timestamp = 'off' wal_keep_segments = '8' wal_level = 'replica' wal_log_hints = 'on' hba_file = '/pgdata/pcl_l702/pg_hba.conf' ident_file = '/pgdata/pcl_l702/pg_ident.conf' $ $ $ $ cat postgresql.base.conf datestyle = 'iso, mdy' default_text_search_config = 'pg_catalog.english' dynamic_shared_memory_type = posix lc_messages = 'en_US.UTF-8' lc_monetary = 'de_CH.UTF-8' lc_numeric = 'de_CH.UTF-8' lc_time = 'de_CH.UTF-8' logging_collector = on log_directory = 'pg_log' log_rotation_age = 1d log_rotation_size = 0 log_timezone = 'Europe/Vaduz' log_truncate_on_rotation = on max_connections = 100 timezone = 'Europe/Vaduz' archive_command = 'test ! -f /tmp/pg_archive_backup_running_on_pcl_l702* && rsync --checksum %p /pgxlog_archive/pcl_l702/%f' archive_mode = on archive_timeout = 1800 cluster_name = pcl_l702 cron.database_name = 'pdb_l72_oiz' # effective_cache_size listen_addresses = '*' log_connections = on log_destination = 'stderr, csvlog' log_disconnections = on log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_line_prefix = '%t : %h=>%u@%d : %p-%c-%v : %e ' log_statement = 'ddl' max_wal_senders = 5 port = 5436 shared_buffers = 512MB shared_preload_libraries = 'auto_explain, pg_stat_statements, pg_cron, pg_statsinfo' wal_buffers = 16MB wal_compression = on wal_level = replica # work_mem 3) 12:00h: primary - standby => Some clients commited some transactions; Failover 12:05h: standby - primary => Some clients connected + commited some transactions; Failover 12:10h: primary - standby On 11/7/19 7:18 AM, Zwettler Markus (OIZ) wrote: > I already asked the Patroni folks. They told me this is not related to > Patroni but Postgresql. ;-) Hard to say without more information: 1) Postgres version 2) Setup/config info 3) Detail if what happened between 12:00 and 12:10 > > - Markus > > > > On 11/7/19 5:52 AM, Zwettler Markus (OIZ) wrote: >> we are using Patroni for management of our Postgres standby databases. >> >> we take our (wal) backups on the primary side based on intervals and >> thresholds. >> our archived wal's are written to a local wal directory first and moved to >> tape afterwards. >> >> we got a case where Patroni switched back and forth sides quickly, e.g.: >> 12:00h: primary - standby >> 12:05h: standby - primary >> 12:10h: primary - standby >> >> we realised that we will not have a wal backup of those wal's generated >> between 12:05h and 12:10h in this scenario. >> >> how can we make sure that the whole wal sequence trail will be backuped? any >> idea? > > Probably best to ask the Patroni folks: > > https://github.com/zalando/patroni#community > >> >> - Markus >> >> > > -- Adrian Klaver adrian.kla...@aklaver.com
11 -> 12 upgrade on Debian Ubuntu
I am in the middle of a project, and it looks like version 12 is now what the Debian/Ubuntu package managers want to update to. I of course, will do this first on a test machine, not the "production", or "develop,met" machines, but I thought i would solicit the group wisdom on this. Are there any things I should watch out for here? Will my data be preserved during this upgrade? We are just beginning to put real data in the "production" instance, and just yesterday, I set up a script to do backups using pg_basebackup. BTW this is the 1st time I have sued this, having used pg_dump in the past. Database is fairly small with just one tablespace if that matters. Thanks for anyone's input. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: AW: broken backup trail in case of quickly patroni switchback and forth
On 11/7/19 7:18 AM, Zwettler Markus (OIZ) wrote: I already asked the Patroni folks. They told me this is not related to Patroni but Postgresql. ;-) Hard to say without more information: 1) Postgres version 2) Setup/config info 3) Detail if what happened between 12:00 and 12:10 - Markus On 11/7/19 5:52 AM, Zwettler Markus (OIZ) wrote: we are using Patroni for management of our Postgres standby databases. we take our (wal) backups on the primary side based on intervals and thresholds. our archived wal's are written to a local wal directory first and moved to tape afterwards. we got a case where Patroni switched back and forth sides quickly, e.g.: 12:00h: primary - standby 12:05h: standby - primary 12:10h: primary - standby we realised that we will not have a wal backup of those wal's generated between 12:05h and 12:10h in this scenario. how can we make sure that the whole wal sequence trail will be backuped? any idea? Probably best to ask the Patroni folks: https://github.com/zalando/patroni#community - Markus -- Adrian Klaver adrian.kla...@aklaver.com
AW: broken backup trail in case of quickly patroni switchback and forth
I already asked the Patroni folks. They told me this is not related to Patroni but Postgresql. ;-) - Markus On 11/7/19 5:52 AM, Zwettler Markus (OIZ) wrote: > we are using Patroni for management of our Postgres standby databases. > > we take our (wal) backups on the primary side based on intervals and > thresholds. > our archived wal's are written to a local wal directory first and moved to > tape afterwards. > > we got a case where Patroni switched back and forth sides quickly, e.g.: > 12:00h: primary - standby > 12:05h: standby - primary > 12:10h: primary - standby > > we realised that we will not have a wal backup of those wal's generated > between 12:05h and 12:10h in this scenario. > > how can we make sure that the whole wal sequence trail will be backuped? any > idea? Probably best to ask the Patroni folks: https://github.com/zalando/patroni#community > > - Markus > > -- Adrian Klaver adrian.kla...@aklaver.com
Re: logical replication - negative bitmapset member not allowed
On Thu, 7 Nov 2019 16:02:21 +0100 Peter Eisentraut wrote: > On 2019-11-05 17:05, Jehan-Guillaume de Rorthais wrote: > >> I have simplified your reproduction steps from the previous message to a > >> test case, and I can confirm that your proposed fix addresses the issue. > > > > Thanks for the feedback and the test case. I wonder if ALTER SUBSCRIPTION > > DISABLE/ENABLE is useful in the test case? > > Turns out it's not necessary. Attached is an updated patch that > simplifies the test even further and moves it into the > 008_diff_schema.pl file. OK. No further comments on my side. Thanks,
Re: SQL SERVER migration to PostgreSql
İlyas Derse schrieb am 07.11.2019 um 14:28: > I'm trying to migration to PostgreSql from SQL Server. I have Stored > Procedures what have output parameters and returning tables.But you > know what, we can not returning tables in stored procedures in > PostgreSql and we can not use output parameters in functions in > PostgreSql. The correct migration path is to rewrite them to set-returning functions and use them in the FROM clause: so instead of sp_foobar 42; use select * from fn_foobar(42); Thomas
Re: broken backup trail in case of quickly patroni switchback and forth
On 11/7/19 5:52 AM, Zwettler Markus (OIZ) wrote: we are using Patroni for management of our Postgres standby databases. we take our (wal) backups on the primary side based on intervals and thresholds. our archived wal's are written to a local wal directory first and moved to tape afterwards. we got a case where Patroni switched back and forth sides quickly, e.g.: 12:00h: primary - standby 12:05h: standby - primary 12:10h: primary - standby we realised that we will not have a wal backup of those wal's generated between 12:05h and 12:10h in this scenario. how can we make sure that the whole wal sequence trail will be backuped? any idea? Probably best to ask the Patroni folks: https://github.com/zalando/patroni#community - Markus -- Adrian Klaver adrian.kla...@aklaver.com
Re: logical replication - negative bitmapset member not allowed
On 2019-11-05 17:18, Andres Freund wrote:
On 2019-11-05 16:02:51 +0100, Peter Eisentraut wrote:
$node_publisher->stop('fast');
+
+
+# TODO:
https://www.postgresql.org/message-id/flat/a9139c29-7ddd-973b-aa7f-71fed9c38d75%40minerva.info
+
+$node_publisher = get_new_node('publisher3');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+$node_subscriber = get_new_node('subscriber3');
+$node_subscriber->init(allows_streaming => 'logical');
+$node_subscriber->start;
Do we really have to create a new subscriber for this test? The creation
of one isn't free. Nor is the amount of test code duplication
neglegible.
I changed that in the v2 patch.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: logical replication - negative bitmapset member not allowed
On 2019-11-05 17:05, Jehan-Guillaume de Rorthais wrote:
I have simplified your reproduction steps from the previous message to a
test case, and I can confirm that your proposed fix addresses the issue.
Thanks for the feedback and the test case. I wonder if ALTER SUBSCRIPTION
DISABLE/ENABLE is useful in the test case?
Turns out it's not necessary. Attached is an updated patch that
simplifies the test even further and moves it into the
008_diff_schema.pl file.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From dcc12ec8315ecb8613190052d4f787cf0554e2c2 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut
Date: Thu, 7 Nov 2019 13:48:59 +0100
Subject: [PATCH v2] Fix negative bitmapset member not allowed error in logical
replication
This happens when we add a replica identity column on a subscriber
that does not yet exist on the publisher, according to the mapping
maintained by the subscriber. Code that checks whether the target
relation on the subscriber is updatable would check the replica
identity attribute bitmap with a column number -1, which would result
in an error. To fix, skip such columns in the bitmap lookup and
consider the relation not updatable. The result is consistent with
the rule that the replica identity columns on the subscriber must be a
subset of those on the publisher, since if the column doesn't exist on
the publisher, the column set on the subscriber can't be a subset.
Reported-by: Tim Clarke
Analyzed-by: Jehan-Guillaume de Rorthais
Discussion:
https://www.postgresql.org/message-id/flat/a9139c29-7ddd-973b-aa7f-71fed9c38d75%40minerva.info
---
src/backend/replication/logical/relation.c | 3 +-
src/test/subscription/t/008_diff_schema.pl | 37 --
2 files changed, 37 insertions(+), 3 deletions(-)
diff --git a/src/backend/replication/logical/relation.c
b/src/backend/replication/logical/relation.c
index f938d1fa48..b386f8460d 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -340,7 +340,8 @@ logicalrep_rel_open(LogicalRepRelId remoteid, LOCKMODE
lockmode)
attnum = AttrNumberGetAttrOffset(attnum);
- if (!bms_is_member(entry->attrmap[attnum],
remoterel->attkeys))
+ if (entry->attrmap[attnum] < 0 ||
+ !bms_is_member(entry->attrmap[attnum],
remoterel->attkeys))
{
entry->updatable = false;
break;
diff --git a/src/test/subscription/t/008_diff_schema.pl
b/src/test/subscription/t/008_diff_schema.pl
index 3ad00eae3b..d1c8fb7061 100644
--- a/src/test/subscription/t/008_diff_schema.pl
+++ b/src/test/subscription/t/008_diff_schema.pl
@@ -3,7 +3,7 @@
use warnings;
use PostgresNode;
use TestLib;
-use Test::More tests => 4;
+use Test::More tests => 5;
# Create publisher node
my $node_publisher = get_new_node('publisher');
@@ -29,7 +29,7 @@
# Setup logical replication
my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
$node_publisher->safe_psql('postgres',
- "CREATE PUBLICATION tap_pub FOR TABLE test_tab");
+ "CREATE PUBLICATION tap_pub FOR ALL TABLES");
$node_subscriber->safe_psql('postgres',
"CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr'
PUBLICATION tap_pub"
@@ -88,5 +88,38 @@
is($result, qq(3|3|3|3),
'check extra columns contain local defaults after apply');
+
+# Check a bug about adding a replica identity column on the subscriber
+# that was not yet mapped to a column on the publisher. This would
+# result in errors on the subscriber and replication thus not
+# progressing.
+#
(https://www.postgresql.org/message-id/flat/a9139c29-7ddd-973b-aa7f-71fed9c38d75%40minerva.info)
+
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE test_tab2 (a int)");
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE test_tab2 (a int)");
+
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub REFRESH PUBLICATION");
+
+# Add replica identity column. (The serial is not necessary, but it's
+# a convenient way to get a default on the new column so that rows
+# from the publisher that don't have the column yet can be inserted.)
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE test_tab2 ADD COLUMN b serial PRIMARY KEY");
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO test_tab2 VALUES (1)");
+
+$node_publisher->wait_for_catchup('tap_sub');
+
+is($node_subscriber->safe_psql('postgres',
+ "SELECT count(*),
min(a), max(a) FROM test_tab2"),
+ qq(1|1|1),
+ 'check replicated inserts on subscriber');
+
+
$node_subscriber->stop;
$node_publisher->stop;
base-commit: e5cfb8cbbe91e73ee92d9e4ab023ca208f3b748a
--
2.23.0
Re: SQL SERVER migration to PostgreSql
On 11/7/19 5:28 AM, İlyas Derse wrote: I'm trying to migration to PostgreSql from SQL Server. I have Stored Procedures what have output parameters and returning tables.But you know what, we can not returning tables in stored procedures in PostgreSql and we can not use output parameters in functions in PostgreSql. What version of Postgres? So i did not find to solves this problem. Anybody have an idea ? Start here: https://www.postgresql.org/docs/11/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS https://www.postgresql.org/docs/11/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING If that does not help then post an example of what you are trying to do. -- Adrian Klaver adrian.kla...@aklaver.com
SQL SERVER migration to PostgreSql
I'm trying to migration to PostgreSql from SQL Server. I have Stored Procedures what have output parameters and returning tables.But you know what, we can not returning tables in stored procedures in PostgreSql and we can not use output parameters in functions in PostgreSql. So i did not find to solves this problem. Anybody have an idea ?
broken backup trail in case of quickly patroni switchback and forth
we are using Patroni for management of our Postgres standby databases. we take our (wal) backups on the primary side based on intervals and thresholds. our archived wal's are written to a local wal directory first and moved to tape afterwards. we got a case where Patroni switched back and forth sides quickly, e.g.: 12:00h: primary - standby 12:05h: standby - primary 12:10h: primary - standby we realised that we will not have a wal backup of those wal's generated between 12:05h and 12:10h in this scenario. how can we make sure that the whole wal sequence trail will be backuped? any idea? - Markus
Re: PostgreSQL && data types in ESQL/C
On Wed, Nov 6, 2019 at 12:32 AM Matthias Apitz wrote:
> Hello,
>
> On our project roadmap to port our LMS (Library Management System) from
> Sybase/Oracle to PostgreSQL we are now in the phase of addressing the
> ESQL/C and C++ code parts (some million lines of code).
>
> I wrote a small ESQL/C test code to see how the various data types are
> handled.
>
> In general: Is there any good manual about ESQL/C in PostgreSQL?
> Because, even if there are standards any implementation has its details.
>
> In detail:
>
> I've created a table with the most used data types:
>
> $ cat mytypes.sql
>
> create table mytypes (
> myint integer,
> mychar char (4),
> mydate date,
> myvchar varchar(81),
> myblob bytea
> ) ;
>
> and have loaded a row with some data which is shown in pgsql as:
>
> $ printf "select * from mytypes;\n" | psql -Usisis -d newsisis
> myint | mychar | mydate | myvchar|myblob
> ---+++--+--
> 1 | char | 08.05.1945 | освобождение |
> \xd0bed181d0b2d0bed0b1d0bed0b6d0b4d0b5d0bdd0b8d0b50a
> (1 Zeile)
>
> in the ESQL/C code the host variables are declared as:
>
> EXEC SQL BEGIN DECLARE SECTION;
> ...
> int myint;
> char mychar[8];
> char mydate[10+1];
> char myvchar[81];
> char myblob[1024];
> ...
> EXEC SQL END DECLARE SECTION;
>
> and the FETCH into these is done with:
>
> EXEC SQL FETCH IN c_statename INTO :myint, :mychar, :mydate,
> :myvchar, :myblob;
>
> which gives with an ESQL/C test pgm which prints the above host
> variables:
>
> $ /usr/local/sisis-pap/pgsql/bin/ecpg embedded.pgc
> $ gcc -m64 -o embedded embedded.c -I/usr/local/sisis-pap/pgsql/include
> -L/usr/local/sisis-pap/pgsql/lib/ -lpq -lecpg
>
> $ ./embedded
> stmt: SELECT myint, mychar, mydate, myvchar, myblob FROM mytypes;
> myint [1]
> mychar [char]
> mydate [08.05.1945]
> myvchar [освобождение]
> myblob [\xd0bed181d0b2d0bed0b1d0bed0b6d0b4d0b5d0bdd0b8d0b50a]
>
> It seems(!):
>
> - an int appears as binary integer
> - all others types (even the column type 'date') appear as C type char*
> - 'date', 'char' and 'varchar' are delivered as '\0' terminated strings
> - 'bytea' appears as '\0' terminated string coded in hex with "\x" in front
>
> Our DBCALL layer must convert these char strings in the data form the
> application layer is expecting, for example a BLOB ('bytea') into a C struct
https://www.postgresql.org/docs/9.1/ecpg-variables.html#ECPG-VARIABLES-TYPE-MAPPING
It looks like there is a workaround for the null terminated strings
and dates. In practice through it looks like you may need to be
prepared to tweak either the ecpg library or your application to get
this to work.
merlin
type SERIAL in C host-struct
Hello,
We're struggling with the following problem (here show in a simplified
case).
We have in a PG 11.4 database a table with two columns: SERIAL, VARCHAR(11).
In the ESQL/C pgm the code is:
EXEC SQL BEGIN DECLARE SECTION;
...
struct {
int ser;
char name [11];
} host_struct;
EXEC SQL END DECLARE SECTION;
an INSERT with
strcpy(host_struct.name, "Sigrid");
host_struct.ser = 0;
EXEC SQL INSERT INTO lina VALUES (:host_struct);
works but, sets the SERIAL column to 0;
an INSERT with
EXEC SQL INSERT INTO lina VALUES (DEFAULT, :host_struct.name);
works correctly and increments the SERIAL on every INSERT:
printf "select * from lina WHERE name = 'Sigrid';\n" | psql -Usisis -d newsisis
lid | name
-+--
28 | Sigrid
29 | Sigrid
0 | Sigrid <*** this was with host_struct.ser = 0;
30 | Sigrid
31 | Sigrid
How the value for host_struct.ser must be given, as we do not want to
name all the struct members in the INSERT statement(s), the real structs
have plenty much columns, some ~30.
Thanks
matttias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: Hunspell as filtering dictionary
Thanks. The problem is that the hunspell dictionary doesn't work with unaccent so it is actually totally useless for languages with accents. If one has to rely on stemming for words with accents, it is just a partial solution and it is not the right solution. Besides, the results returned by the hunspell implementation in postgresql are incorrect. As you mentioned, it shouldn't return "con" and "tract" for "contract". I also noticed many other weird results with other words in French. They might have a bug in their code. I ended up using ts_debug() with a simple stopword file in my own tokenizer written with pllua that calls libhunspell directly using luajit and ffi. I also wrote my own unaccent in Lua using the unaccent extension rules. It is now two times faster to index French text and it gives much better results. It produces a tsvector. Words returned by libhunspell stem() function get a lower weight D and keep the same position as the original word. My conclusion is that hunspell in postgres is useless for me at least because it should be a filtering dictionary and it produces strange results that pollute the original text. I also think that the current implementation of TEXT SEARCH configuration is not usable for serious purposes. It is too limited. Solr configuration, while more complex, does a much better job. Le mer. 6 nov. 2019 à 16:50, Hugh Ranalli a écrit : > On Tue, 5 Nov 2019 at 09:42, Bibi Mansione wrote: > >> Hi, >> I am trying to create a ts_vector from a French text. Here are the >> operations that seem logical to perform in that order: >> >> 1. remove stopwords >> 2. use hunspell to find words roots >> 3. unaccent >> > > I can't speak to French, but we use a similar configuration in English, > with unaccent first, then hunspell. We found that there were words that > hunspell didn't recognise, but instead pulled apart (for example, > "contract" became "con" and "tract"), so I wonder if something similar is > happening with "découvrir." To solve this, we put a custom dictionary with > these terms in front of hunspell. Unaccent definitely has to be called > first. We also modified hunspell with a custom stopwords file, to eliminate > select other terms, such as profanities: > > -- We use a custom stopwords file, to filter out other terms, such as > profanities > ALTER TEXT SEARCH DICTIONARY > hunspell_en_ca ( > Stopwords = our_custom_stopwords > ); > > -- Adding english_stem allows us to recognize words which hunspell > -- doesn't, particularly acronyms such as CGA > ALTER TEXT SEARCH CONFIGURATION > our_configuration > ALTER MAPPING FOR > asciiword, asciihword, hword_asciipart, > word, hword, hword_part > WITH > unaccent, our_custom_dictionary, hunspell_en_ca, english_stem > ; > > There was definitely a fair bit of trial and error to determine the > correct order and configuration. >
