Postgres Automated Failover

[AI Rumman]

Hi,

I am planning to use Postgresql with TimescaleDb extension. I have to
design a system similar to AWS RDS which supports automated failover,
transparent minor version upgrades etc.
In early days, I worked with PgPool II to enable heartbeat between the
Postgres servers. Is there any new tool to achieve the same by which I can
design a transparent failover system for Postgres?

Also is there any multi-master Postgresql solution? Is Postgres-XC
production ready?

Any suggestions is appreciated.

Thanks.

Re: Postgres Automated Failover

[AI Rumman]

Thanks, I'll check it out.

On Thu, Jan 17, 2019 at 9:06 AM Jehan-Guillaume (ioguix) de Rorthais <
iog...@free.fr> wrote:

> On Thu, 17 Jan 2019 08:32:48 -0500
> AI Rumman  wrote:
>
> > Hi,
> >
> > I am planning to use Postgresql with TimescaleDb extension. I have to
> > design a system similar to AWS RDS which supports automated failover,
> > transparent minor version upgrades etc.
> > In early days, I worked with PgPool II to enable heartbeat between the
> > Postgres servers. Is there any new tool to achieve the same by which I
> can
> > design a transparent failover system for Postgres?
>
> There is multiple HA solutions, none are "transparent failover" though.
> They
> are just automated-failover. A rollback-ed transaction because of failover
> will
> never be transparent from the application point of view.
>
> Look at Patroni or PAF, depending on what you want to achieve.
>

Timescale database is going towards emergency autovacuum

[AI Rumman]

We are running timescale pg_prometheus with separating each services in
schema. So in one database we have:

> >> schema_a
>view: metrics
> tables: metrics_copy, metrics_labels, metrics_values
> >> schema_b
>view: metrics
> tables: metrics_copy, metrics_labels, metrics_values
> and so on


We have multiple databases with size:

> datname   size   age(datfrozenxid)
> --
> postgres8973 kB51018138
> testdb9165 kB51018138
> template17649 kB51018138
> template07473 kB51018138
> testdb17781 kB51018138
> db16334 MB51018138
> db274 MB51018138
> db39645 kB51018138
> db411 MB51018138
> db4759 MB51018138


We have only 25 days of data and are also using a materialized view. I am
using the following query to check my emergency autovacuum threshold:

> select ROUND(100*(max(age(datfrozenxid))/(
> ( select setting AS value FROM pg_catalog.pg_settings   WHERE name =
> 'autovacuum_freeze_max_age' ))::float)
>   ) as percent_towards_wraparound
> from pg_database

Now each day I can see it is growing towards the limit. Now it is showing
26%.
How can we save our system from wraparound issue?
Someone please give some suggestions.

Thanks.

template0 is having high age of datforzenxid

[AI Rumman]

Hi,

I am running Postgresql 10 where I can see the template0 database is having
longest datfrozenxid:
```db_name  age_of_datfrozenxid
--
postgres251365
template1234574
template075366462
db1253097
db2250649
db3250649
db414641
db514214```

Initially I had high age for all the databases and I ran "vacuum freeze"
which brought down the age of other databases. But how can I do the same
for template0?
Please advise.

Thanks.

CONCAT function adding extra characters

[AI Rumman]

I am using Postgresql 10 and seeing a strange behavior in CONCAT function
when I am concatenating double precision and int with a separator.

select concat('41.1'::double precision,':', 20);
> Result:
> 41.1014:20


Value 41.1 which double precision converts to 41.100014.

Is that expected?

Thanks.

Re: CONCAT function adding extra characters

[AI Rumman]

I saw that problem when I was running the query from DBeaver.
Got my answer.

Thanks & Regards.

On Tue, Jun 15, 2021 at 12:18 PM Pavel Stehule 
wrote:

>
>
> út 15. 6. 2021 v 21:07 odesílatel Tom Lane  napsal:
>
>> AI Rumman  writes:
>> > I am using Postgresql 10 and seeing a strange behavior in CONCAT
>> function
>> > when I am concatenating double precision and int with a separator.
>>
>> > select concat('41.1'::double precision,':', 20);
>> >> Result:
>> >> 41.1014:20
>>
>> What have you got extra_float_digits set to?
>>
>>
> postgres=# set extra_float_digits to 3;
> SET
> postgres=# select concat('41.1'::double precision,':', 20);
> ┌┐
> │ concat │
> ╞╡
> │ 41.1014:20 │
> └┘
> (1 row)
>
> Pavel
>
>
>
>> regards, tom lane
>>
>>
>>