Re: Logical replication from Rds into on-premise
Dave Cramer www.postgres.rocks On Mon, 26 Jul 2021 at 18:43, Cory Nemelka wrote: > > On Mon, Jul 26, 2021 at 12:06 PM Rama Krishnan > wrote: > >> Hi all, >> >> I have a postgres server on Aws RDS no i want to replicate the data or >> logical replication into the on-premise server. I have gone through DMS >> provides the service buy it pricing was high. Do we have any option or >> method to achieve this? >> >> Thanks >> >> RamaKrishnan >> > > I would start here: > https://www.postgresql.org/docs/12/sql-createpublication.html > > make sure all your tables have primary keys. :D > Does RDS allow logical replication
Re: Logical replication from Rds into on-premise
On Tue, Jul 27, 2021 at 4:38 AM Dave Cramer wrote: > > Does RDS allow logical replication > Yes, it does. I believe it was patched for v9.6, but v10 and above support it out of the box, and the RDS version of PostgreSQL shares that support. I have used it with v10 and v11, and it works exactly like the standard PG docs say it should. - Miles
sort order
Hi,
context:
one postgres 12 on centos 7
one postgres 12 on windows 10
both on machines with french as default
the centos 7 lc_collate and lc_ctype: fr_FR.UTF_8
the w10 lc_collate and lc_ctype: French_France.1252
create table test (ble text, id serial primary key);
insert into test(ble) values(' ');
insert into test(ble) values('Marc');
insert into test(ble) values(' Marc');
insert into test(ble) values('marc');
insert into test(ble) values(' marc');
insert into test(ble) values('bobo');
insert into test(ble) values(' bobo'):
insert into test(ble) values('élise');
differences include a french é character, and some white spaces at the
beginning.
then select * from test order by ble;
centos result:
ble | id
---+
| 3
bobo | 2
bobo | 1
élise | 6
marc | 5
marc | 4
Marc | 8
Marc | 7
(8 lignes)
w10 result:
ble | id
---+
| 3
bobo | 1
marc | 4
Marc | 7
élise | 8
bobo | 2
marc | 5
Marc | 6
(8 lignes)
so, obviously, both lc_collate knows about the é
but obviously, too, they do behave differently on the impact of the
beginning white space.
I didn't see anything about this behaviour on the doc, unless the reference
at the libc should be understood as please read and test libc doc on each
platform.
So my first question is: why ?
My second question is: how to make the centos postgres behave like the w10
one ??
ie. knowing about french characters AND taking beginning white spaces into
account ?
thanks,
regards,
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com
Re: Logical replication from Rds into on-premise
Hi Dave, Rds support logical replication but the question of how to make replication between RDS into on premise On Tue, 27 Jul, 2021, 17:08 Dave Cramer, wrote: > > Dave Cramer > www.postgres.rocks > > > On Mon, 26 Jul 2021 at 18:43, Cory Nemelka wrote: > >> >> On Mon, Jul 26, 2021 at 12:06 PM Rama Krishnan >> wrote: >> >>> Hi all, >>> >>> I have a postgres server on Aws RDS no i want to replicate the data or >>> logical replication into the on-premise server. I have gone through DMS >>> provides the service buy it pricing was high. Do we have any option or >>> method to achieve this? >>> >>> Thanks >>> >>> RamaKrishnan >>> >> >> I would start here: >> https://www.postgresql.org/docs/12/sql-createpublication.html >> >> make sure all your tables have primary keys. :D >> > > Does RDS allow logical replication >
Re: Logical replication from Rds into on-premise
Awesome, thanks! Can you please share docs my Rds version is 12.3 On Tue, 27 Jul, 2021, 19:00 Miles Elam, wrote: > On Tue, Jul 27, 2021 at 4:38 AM Dave Cramer > wrote: > >> >> Does RDS allow logical replication >> > > Yes, it does. I believe it was patched for v9.6, but v10 and above support > it out of the box, and the RDS version of PostgreSQL shares that support. I > have used it with v10 and v11, and it works exactly like the standard PG > docs say it should. > > - Miles > >
PHP: query with parameter for SET
Hi everyone,
Using PHP (or indeed maybe more generally), is it possible to issue a
SET TIME ZONE statement with a parameter for the new runtime setting?
In a PHP application I'm issuing a SET TIME ZONE command just after the
database connection is created, and for security I'd like to pass the
time zone value in a parameter, as it's coming from a configuration file
- something like this:
$pdo = new PDO(/* DSN goes here */);
$statement = $pdo->prepare('set time zone :tz');
$statement->execute([':tz' => 'Europe/Dublin']);
However, in the logs I see:
ERROR: syntax error at or near "to" at character 15
STATEMENT: set time zone $1
Is this possible? I've also tried the "set timezone ..." variant with
similar results. I'm wondering too if the limitation (if such it is) is
in PHP or whether it's a Postgres thing.
Thanks,
Ray.
--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie
Re: PHP: query with parameter for SET
On 7/27/21 10:10 AM, Ray O'Donnell wrote:
Hi everyone,
Using PHP (or indeed maybe more generally), is it possible to issue a
SET TIME ZONE statement with a parameter for the new runtime setting?
In a PHP application I'm issuing a SET TIME ZONE command just after the
database connection is created, and for security I'd like to pass the
time zone value in a parameter, as it's coming from a configuration file
- something like this:
$pdo = new PDO(/* DSN goes here */);
$statement = $pdo->prepare('set time zone :tz');
$statement->execute([':tz' => 'Europe/Dublin']);
However, in the logs I see:
ERROR: syntax error at or near "to" at character 15
STATEMENT: set time zone $1
Is this possible? I've also tried the "set timezone ..." variant with
similar results. I'm wondering too if the limitation (if such it is) is
in PHP or whether it's a Postgres thing.
You probably want set_config():
https://www.postgresql.org/docs/12/functions-admin.html#FUNCTIONS-ADMIN-SET
9.26.1. Configuration Settings Functions
Thanks,
Ray.
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: PHP: query with parameter for SET
Hi
út 27. 7. 2021 v 19:10 odesílatel Ray O'Donnell napsal:
> Hi everyone,
>
> Using PHP (or indeed maybe more generally), is it possible to issue a
> SET TIME ZONE statement with a parameter for the new runtime setting?
>
> In a PHP application I'm issuing a SET TIME ZONE command just after the
> database connection is created, and for security I'd like to pass the
> time zone value in a parameter, as it's coming from a configuration file
> - something like this:
>
> $pdo = new PDO(/* DSN goes here */);
>
> $statement = $pdo->prepare('set time zone :tz');
> $statement->execute([':tz' => 'Europe/Dublin']);
>
> However, in the logs I see:
>
> ERROR: syntax error at or near "to" at character 15
> STATEMENT: set time zone $1
>
> Is this possible? I've also tried the "set timezone ..." variant with
> similar results. I'm wondering too if the limitation (if such it is) is
> in PHP or whether it's a Postgres thing.
>
When you use client side prepared statements, then it can be possible. But
I don't know how it works in PHP.
Server side prepared statements are not possible in this case. "set"
statement has not execution plan. But you You can use Adrian's proposal,
and you can prepare the query 'select set_config($1, false)'
Regards
Pavel
> Thanks,
>
> Ray.
>
> --
> Raymond O'Donnell // Galway // Ireland
> r...@rodonnell.ie
>
>
>
Re: PHP: query with parameter for SET
On 27/07/2021 18:20, Pavel Stehule wrote: When you use client side prepared statements, then it can be possible. But I don't know how it works in PHP. Server side prepared statements are not possible in this case. "set" statement has not execution plan. But you You can use Adrian's proposal, and you can prepare the query 'select set_config($1, false)' Thanks Pavel - that makes sense. Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: sort order
Re-reading my post, I see that even the élise is not sorted correctly on
w10...
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com
On Tue, Jul 27, 2021 at 7:07 PM Marc Millas wrote:
> Hi,
>
> context:
> one postgres 12 on centos 7
> one postgres 12 on windows 10
> both on machines with french as default
> the centos 7 lc_collate and lc_ctype: fr_FR.UTF_8
> the w10 lc_collate and lc_ctype: French_France.1252
>
>
> create table test (ble text, id serial primary key);
> insert into test(ble) values(' ');
> insert into test(ble) values('Marc');
> insert into test(ble) values(' Marc');
> insert into test(ble) values('marc');
> insert into test(ble) values(' marc');
> insert into test(ble) values('bobo');
> insert into test(ble) values(' bobo'):
> insert into test(ble) values('élise');
>
> differences include a french é character, and some white spaces at the
> beginning.
>
> then select * from test order by ble;
>
> centos result:
> ble | id
> ---+
>| 3
> bobo | 2
> bobo | 1
> élise | 6
> marc | 5
> marc | 4
> Marc | 8
> Marc | 7
> (8 lignes)
>
> w10 result:
> ble | id
> ---+
>| 3
> bobo | 1
> marc | 4
> Marc | 7
> élise | 8
> bobo | 2
> marc | 5
> Marc | 6
> (8 lignes)
>
> so, obviously, both lc_collate knows about the é
> but obviously, too, they do behave differently on the impact of the
> beginning white space.
>
> I didn't see anything about this behaviour on the doc, unless the
> reference at the libc should be understood as please read and test libc doc
> on each platform.
> So my first question is: why ?
> My second question is: how to make the centos postgres behave like the w10
> one ??
> ie. knowing about french characters AND taking beginning white spaces into
> account ?
>
> thanks,
>
> regards,
>
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>
Re: sort order
Marc Millas writes: > Re-reading my post, I see that even the élise is not sorted correctly on > w10... Sort orders aren't even particularly guaranteed on different releases of the same platform, let alone totally different platforms. glibc made major changes to their collation rules not long ago: https://wiki.postgresql.org/wiki/Locale_data_changes At one time we thought that migrating to ICU sorting might bring a little more stability to this, but I think what we've found so far is that it's just different :-( regards, tom lane
Re: PostgreSQL reference coffee mug
El día lunes, julio 26, 2021 a las 09:05:39a. m. +0200, Pavel Stehule escribió: > po 26. 7. 2021 v 8:56 odesílatel Matthias Apitz napsal: > > > > > Hello, > > > > Nearly 20 years ago, I ordered some 50 vi-reference coffee mugs like this > > one here (not exactly the same, but to give you an idea): > > > > https://www.getdigital.eu/vi-reference-mug.html > > > > for our vi-lovers in-house and the admins of our customers. > > > > I'd like to do the same now with a reference mug for PostgreSQL, for > > example with the most important Meta-commands, sys tables, etc. > > I have nearby a copy shop which would produce it based on a JPEG file. > > > > Before designing our own, I wanted to ask here if such a beast exists > > already or, if not, maybe even a Reference Card in PDF or JPEG to use it > > as a > > starting point. > > > > Thanks in advance for any hints. > > > > I have this in czech language - maybe google translator > https://translate.google.com/ helps > > https://postgres.cz/files/tahak_postgresql-13.pdf > Thank you, Pavel. This is ofc to much for a coffee mug. For using it as a Reference Card in paper form, it's a pity that it is not written in English. I'm working on my own for the mug and will publish the PDF and libreoffice ODT version here. The max size of the image for the mug is 7.5cm high x 16cm around the body of the coffee mug. A question to PostgreSQL.org: Are we allowed to use the PostgreSQL logo on such produced mugs without selling them, just as a gift. And can we put our own logo together with the one of PostgreSQL on the same coffee mug. To whom our Legal Dept. would have to ask this officially? Or is it just free and allowed? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Tear it down! Defund the Humboldt Forum! https://www.jungewelt.de/artikel/406715.humboldt-forum-feudaler-themenpark.html
Re: PostgreSQL reference coffee mug
> On 27 Jul 2021, at 20:32, Matthias Apitz wrote: > A question to PostgreSQL.org: Are we allowed to use the PostgreSQL logo > on such produced mugs without selling them, just as a gift. > And can we put our own logo together with the one of PostgreSQL on the > same coffee mug. > > To whom our Legal Dept. would have to ask this officially? Or is it just > free and allowed? Use of the PostgreSQL logo falls under the project Trademark Policy, which you can find here: https://www.postgresql.org/about/policies/trademarks/ There is a point of contact listed there as well in case there are more detailed questions. -- Daniel Gustafsson https://vmware.com/
RDS Proxy war stories?
Hi, Does anyone here have experience to share regarding Amazon's RDS Proxy? Subjective or quantitative, positive or negative, anything you have is great. (I've found information to be thin on the ground, probably because RDS Proxy has been around for such a short time (just over a year). As you can imagine, I'm a bit hesitant to adopt something so new[1].) Thanks in advance! [1] You may be wondering why I don't just use PgBouncer, which is super-stable and -mature. The answer is that RDS Proxy has a few nice affordances not present in PgBouncer: - Automatic re-pointing of open client conns when an RDS failover happens. - Automatic integration with IAM authentication. - Less DevOps code to write. These aren't must-haves; if they came at the cost of overall stability, I would choose PgBouncer. Thanks again, -- Quinn Weaver https://fairpath.com/files/
Re: RDS Proxy war stories?
On 2021-Jul-27, Quinn David Weaver wrote: > Hi, > > Does anyone here have experience to share regarding Amazon's RDS > Proxy? Subjective or quantitative, positive or negative, anything you > have is great. I know one instance of its use. The RDS instance it serves is very large (it used the largest instance that was available in RDS, but the user managed to shrink it to smaller ones as we optimized things) and the concurrency is pretty high. It worked well. No stability complaints. -- Álvaro Herrera
I need another option.
Hello Everyone . "c --clean (drop) database objects before recreating", is there any other option for this. please let me know
Re: I need another option.
On Wed, Jul 28, 2021, 11:10 AM obi reddy wrote: > Hello Everyone . "c --clean (drop) database objects before recreating", > is there any other option for this. please let me know > You'll have to give more information on what you see and what you want or does not work the way you would want. Only then, one can provide some helpful information. also, look at original docs for pg_dump(all) and pg_restore command line options. >
How postgres is refreshing TLS certificates
Hi all, We are working on a activity in which I need to refresh the TLS certificate without restarting the my application pod. This feature is already there in Postgres. Can anyone please suggest us how postgres is implemented the same? Regards Tarkeshwar
