SV: Writing Postgres Extensions in C on Windows
Nothing special with the code for the extension itself. Same as other platforms. Building is another matter. I have written about compiling pgaudit on Windows on recent Postgres releases here. This could perhaps supplement the below blog. https://github.com/njesp/build_pgaudit_on_windows Regards Niels -Oprindelig meddelelse- Fra: Thomas Munro Sendt: 7. januar 2020 21:56 Til: İlyas Derse Cc: pgsql-generallists.postgresql.org Emne: Re: Writing Postgres Extensions in C on Windows On Wed, Jan 8, 2020 at 4:32 AM İlyas Derse wrote: > I want to register C code to PostgreSql on Windows. So I think, I have to > make a extension for PostgreSql. But I did not find to written extension on > windows. Do you have an idea ? I don't do Windows myself but this blog from Craig Ringer looks like a good starting point: https://www.2ndquadrant.com/en/blog/compiling-postgresql-extensions-visual-studio-windows/
Re: How to shorten a chain of logically replicated servers
On Tue, 2020-01-07 at 23:17 -0800, Mike Lissner wrote: > > You'd have to suspend all data modification on A in that interval. > > I know how to stop the DB completely, but I can't think of any obvious > ways to make sure that it doesn't get any data modification for a > period of time. Is there a trick here? This is feeling a bit hopeless. The simplest solution would be to stop the applications that use PostgreSQL. You could block client connections using a "pg_hba.conf" entry (and kill the established connections). Another option can be to set "default_transaction_read_only = on", but that will only work if the clients don't override it explicitly. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Writing Postgres Extensions in C on Windows
Thanks, I'll check it out. Thomas Munro , 7 Oca 2020 Sal, 23:56 tarihinde şunu yazdı: > On Wed, Jan 8, 2020 at 4:32 AM İlyas Derse wrote: > > I want to register C code to PostgreSql on Windows. So I think, I have > to make a extension for PostgreSql. But I did not find to written extension > on windows. Do you have an idea ? > > I don't do Windows myself but this blog from Craig Ringer looks like a > good starting point: > > > https://www.2ndquadrant.com/en/blog/compiling-postgresql-extensions-visual-studio-windows/ >
Re: Upgrade PostgreSQL 9.6 to 10.6
Sorry for the confusion, I tried to install this extension on 9.6 and it seems to be not working. Does 9.6 PostGreSQL supports logical replication ? On Tue, Jan 7, 2020 at 4:56 PM github kran wrote: > > On Tue, Dec 10, 2019 at 2:53 AM Andreas Kretschmer < > andr...@a-kretschmer.de> wrote: > >> >> >> Am 09.12.19 um 23:37 schrieb github kran: >> > Great, thanks Andreas, So this seems to be a good feature using the >> > core concept of replication. Can I use this extension and do the major >> > upgrade without paying ?. >> >> yes, this extension is free. >> >> >> Regards, Andreas >> > > Hello Andreas, >As a follow up on this thread, I want to ask if this extension works on > postgreSQL version 10.6 ? and also does this extension can be installed on > Aurora version of PostGreSQL.?. if it doesnt do you have any >other alternative to make this work on Aurora engine ? > >Thanks !! > > > >> >> -- >> 2ndQuadrant - The PostgreSQL Support Company. >> www.2ndQuadrant.com >> >>
Re: Upgrade PostgreSQL 9.6 to 10.6
On Wed, Jan 8, 2020 at 7:20 AM github kran wrote: > Sorry for the confusion, I tried to install this extension on 9.6 and it > seems to be not working. Does 9.6 PostGreSQL supports logical replication ? > No. See the top of this page with supported versions listed. Prior to v10, pg_logical extension was often used. https://www.postgresql.org/docs/current/logical-replication.html
Re: Setting up an environment of EDB Advance server
On Wed, 8 Jan 2020 at 02:15, Daulat Ram wrote: > Hi team, > > > > We would need your help in setting up an environment of EDB Advance Server > 11 on Dev CentOS 7 VM. > > > >1. We need to create three database with separate table spaces : > > > > Test1 > > Test2 > > Test3 > > What would be the good strategy for the setup with regards to the > tablespaces? > > Can we create a separate cluster for each database: > "EDB Advanced Server 11" is a proprietary product of a company called Enterprise DB, and is substantially different from Postgres. For support relating to their product, you should really contact the vendor. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
Re: How to shorten a chain of logically replicated servers
That's a good trick, thanks again for the help. Boy, this promises to be a dumb process! I'm unqualified to guess at what might make this easier, but it does seem like something that should have some kind of low-level tools that could do the job. On Wed, Jan 8, 2020 at 1:53 AM Laurenz Albe wrote: > > On Tue, 2020-01-07 at 23:17 -0800, Mike Lissner wrote: > > > You'd have to suspend all data modification on A in that interval. > > > > I know how to stop the DB completely, but I can't think of any obvious > > ways to make sure that it doesn't get any data modification for a > > period of time. Is there a trick here? This is feeling a bit hopeless. > > The simplest solution would be to stop the applications that use PostgreSQL. > > You could block client connections using a "pg_hba.conf" entry > (and kill the established connections). > > Another option can be to set "default_transaction_read_only = on", > but that will only work if the clients don't override it explicitly. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com >
Is it safe to transfer logical replication publication/subscription?
Hi all, this is a follow up from an earlier question I asked about shortening a chain of logically replicating servers. Right now we have three servers replicating like this: A --> B --> C And we want to remove B so that we have: A --> C Is it possible to DROP the subscription on B to A and then to SUBSCRIBE C to the previously used publication on A without losing data? E.g., assuming the following: - "A" has a PUBLICATION named "A-to-B-Pub" that "B" subscribes to. - "C" subscribes to "B" with a subscription named "B-to-C-Sub". Would this work? 1. On B, DROP the subscription to "A-to-B-Pub". 2. Let any cached changes on B flush to C. Give it an hour to be sure. 3. On C ALTER "B-to-C-Sub" to subscribe to the now-used "A-to-B-Pub" on A. Seems like this would either work perfectly or totally fail. Any ideas? Thanks for any help, Mike
Re: Is it safe to transfer logical replication publication/subscription?
On 2020-01-08 22:22, Mike Lissner wrote: Hi all, this is a follow up from an earlier question I asked about shortening a chain of logically replicating servers. Right now we have three servers replicating like this: A --> B --> C And we want to remove B so that we have: A --> C Is it possible to DROP the subscription on B to A and then to SUBSCRIBE C to the previously used publication on A without losing data? What you are not taking into account here are replication slots, which are the low-level mechanism to keep track of what a replication client has consumed. When you drop the subscription on B, that (by default) also drops the associated replication slot on A, and therefore you lose the information of how much B has consumed from A. (This assumes that there is concurrent write activity on A, otherwise this is uninteresting.) What you need to do instead is disassociate the B-from-A subscription from the replication slot on A, then let all changes from B trickle to C, then change the C-from-B subscription to replicate from A and use the existing replication slot on A. See https://www.postgresql.org/docs/current/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT for details. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Is it safe to transfer logical replication publication/subscription?
That's a great point, thanks. The DROP SUBSCRIPTION notes say you can: > Disassociate the subscription from the replication slot by executing ALTER > SUBSCRIPTION ... SET (slot_name = NONE). After that, DROP SUBSCRIPTION will > no longer attempt any actions on a remote host. I'll read some more about the replication slots themselves (I did read about them a while back), but doing the above seems like a good way to break B from A, before resubscribing C to A instead? I feel like this is getting warmer. Thanks for the reply. I really appreciate it. Mike On Wed, Jan 8, 2020 at 2:46 PM Peter Eisentraut wrote: > > On 2020-01-08 22:22, Mike Lissner wrote: > > Hi all, this is a follow up from an earlier question I asked about > > shortening a chain of logically replicating servers. Right now we have > > three servers replicating like this: > > > > A --> B --> C > > > > And we want to remove B so that we have: > > > > A --> C > > > > Is it possible to DROP the subscription on B to A and then to > > SUBSCRIBE C to the previously used publication on A without losing > > data? > > What you are not taking into account here are replication slots, which > are the low-level mechanism to keep track of what a replication client > has consumed. When you drop the subscription on B, that (by default) > also drops the associated replication slot on A, and therefore you lose > the information of how much B has consumed from A. (This assumes that > there is concurrent write activity on A, otherwise this is uninteresting.) > > What you need to do instead is disassociate the B-from-A subscription > from the replication slot on A, then let all changes from B trickle to > C, then change the C-from-B subscription to replicate from A and use the > existing replication slot on A. > > See > https://www.postgresql.org/docs/current/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT > for details. > > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Upgrade PostgreSQL 9.6 to 10.6
Great I see its supported based on the link but the problem is we are locked into a Aurora RDS and we can't use logical replication on that engine. Any thoughts that we can use some alternate mechanism to migrate the delta data from database 1 ( Running 9.6) to database 2 ( Running 10.6 ) after we do the pg_dump and pg_restore ?. On Wed, Jan 8, 2020 at 10:25 AM Michael Lewis wrote: > On Wed, Jan 8, 2020 at 7:20 AM github kran wrote: > >> Sorry for the confusion, I tried to install this extension on 9.6 and it >> seems to be not working. Does 9.6 PostGreSQL supports logical replication ? >> > > > No. See the top of this page with supported versions listed. Prior to v10, > pg_logical extension was often used. > https://www.postgresql.org/docs/current/logical-replication.html >
Re: Upgrade PostgreSQL 9.6 to 10.6
> On Jan 8, 2020, at 15:56, github kran wrote: > > Great I see its supported based on the link but the problem is we are locked > into a Aurora RDS and we can't use logical replication on that engine. You can use pglogical on RDS PostgreSQL 9.6. -- -- Christophe Pettus x...@thebuild.com
Re: Upgrade PostgreSQL 9.6 to 10.6
On Wed, Jan 8, 2020 at 5:57 PM Christophe Pettus wrote: > > > > On Jan 8, 2020, at 15:56, github kran wrote: > > > > Great I see its supported based on the link but the problem is we are > locked into a Aurora RDS and we can't use logical replication on that > engine. > > You can use pglogical on RDS PostgreSQL 9.6. > > -- > -- Christophe Pettus >x...@thebuild.com > > You are right on RDS but I believe the problem is on Aurora PostgreSQL where the pglogical throws an error during installation. Are you aware if this works on Aurora PostGreSQL
what to do after a failover
I run a master and standby setup with Postgresql 11. The systems are identical from a hardware and software setup. If the master goes down I can do a pg_ctl promote on the standby and point my applications to use the standby (new master). Once the original master is online, when is an appropriate time to fail back over? And are there any other things besides promote after the failover is done? -- --- Get your facts first, then you can distort them as you please.--
Re: what to do after a failover
On Wed, Jan 08, 2020 at 11:06:28PM -0500, Rita wrote: > I run a master and standby setup with Postgresql 11. The systems are > identical from a hardware and software setup. If the master goes down I > can do a pg_ctl promote on the standby and point my applications to use the > standby (new master). > > Once the original master is online, when is an appropriate time to fail > back over? And are there any other things besides promote after the > failover is done? Make sure that you still have an HA configuration able to handle multiple degrees of failures with always standbys available after a promotion. The options available to rebuild your HA configuration after a failover depend on the version of PostgreSQL you are using. After a failover the most simple solution would be to always recreate a new standby from a base backup taken from the freshly-promoted primary, though it can be costly depending on your instance. You could also use pg_rewind (available in core since 9.5) to recycle the previous primary and reuse it as a standby of the new promoted custer. Note that there are community-based solutions for such things, like pg_auto_failover or pacemaker-based stuff just to name two. These rely on more complex architectures, where a third node is present to monitor the others (any sane HA infra ought to do at least that to be honest). -- Michael signature.asc Description: PGP signature
How can I pushdown of functions used in targetlist with FDW ?
How can I pushdown of functions used in targetlist with FDW ? I know non-built-in functions can pushdown with WHERE clauses. https://www.postgresql.org/docs/12/postgres-fdw.html#id-1.11.7.42.10 But I would like to pushdown function when use targetlist. For example, I would like to change "Remote SQL: SELECT h FROM public.hs" to "Remote SQL: SELECT avals(h) FROM public.hs" in bellow case. postgres=# explain (verbose) SELECT avals(h) FROM tab_hstore; QUERY PLAN -- Foreign Scan on public.tab_hstore (cost=100.00..157.52 rows=1462 width=32) Output: avals(h) Remote SQL: SELECT h FROM public.hs How to pushdown function in targetlist with FDW? And if it can not pushdown, do you have a plan to add features? Regards, Shigeo Hirose
Re: Postgres streaming replication
On Wed, Jan 08, 2020 at 05:04:21AM +, Daulat Ram wrote: > I have to implement the streaming replication for our prod environment. > Can you please share the list of parameters to setup the PostgreSQL > 11 streaming replication with continuous archiving and give clarity > on the below. > > * Do we need to enable the archive_mode and archive_command > parameters at standby side if implementing PostgreSQL 11 > streaming replication with continuous archiving ? > * What is the benefits for implementing streaming replication > with continuous archiving over the PostgreSQL Streaming > replication? I think that you need to do two things and take the necessary time to do both: 1) List on a sheet of paper all your requirements, your expectations and what kind of tradeoffs you are ready to make for your cluster configuration when it comes to potential data loss, and the amount of data retention you are willing to have around. There is no way to tell what's good or not for your product if you don't know that first. 2) Read the documentation: https://www.postgresql.org/docs/current/high-availability.html -- Michael signature.asc Description: PGP signature
Re: How can I pushdown of functions used in targetlist with FDW ?
shigeo Hirose writes: > How can I pushdown of functions used in targetlist with FDW ? There is, AFAIK, no provision for that. There's not a lot of reason to consider adding it either, because there's no reason to suppose that the remote node can run such a function any faster than the local node. So the difficulty and risk of determining/assuming that f(x) on the local node is the same as f(x) on the remote node doesn't seem like it'd be repaid. You can force matters by making a foreign table that points to a view on the remote side, where the view includes the function call as an output column. It wouldn't be perfectly transparent of course; you'd have to modify the local query to refer to that column rather than calling the function explicitly. regards, tom lane
Re: Upgrade PostgreSQL 9.6 to 10.6
On Wed, Jan 8, 2020 at 8:52 PM github kran wrote: > You are right on RDS but I believe the problem is on Aurora PostgreSQL > where the pglogical throws an error during installation. Are you aware if > this works on Aurora PostGreSQL > It seems like this question should be sent to AWS support for Aurora if you are wanting to upgrade an Aurora 9.6 instance to 10x also in Aurora.
