Doubts about replication..

You want the replication for backup purposes. What is the use case for your
backups: Is it disaster recovery? Is it archiving historical data? Is it
failover? Is it off-site backup?

If you outline your needs then the proper solution can be offered. There
are other methods than just using the built-in binary file replication.

Based on your use of the words "as simple backup" it seems to me you would
be better off just using pg_dump periodically to copy the database to the
backup system. I would use the same version of pg_dump as the database for
maximal compatibility on restoring to that version.

As for your versions, all of the 9.4.x should be upgraded to the latest
9.4.x release there is. This is a simple upgrade and restart, and very safe
to do. You will get many fixed bugs, some of which could cause data loss.
Similarly for the 9.5.x release.

On Thu, Apr 19, 2018 at 1:57 PM, Edmundo Robles <edmundo@sw-argos.com>
wrote:

On 04/19/2018 10:57 AM, Edmundo Robles wrote:

I have several versions of postgres 9.4.5, 9.4.4, 9.4.15 (3), 9.5.3
in different versions of Debian 7.6, 7.8, 7.11, 8.5 and 8.6.

I need to replicate the databases and I have clear that I must update
all to one version.
My main question is, Do you  recommended me update to 9.6 or better
update to 10?.

If you have the choice go with 10 as you get an additional year of
community support.

Actually, is not the goal have high availability . I will use
replication as simple backup.
For reasons of $$$ I can only have 1 server in which I will replicate
the 6 databases.

Do you recommend using a postgres service for the 6 databases?, or
better, I think,   I must run  a postgres service in different ports,
for each  database?.

I am assuming you are going to be using some form of logical replication
as binary replication will not work between Postgres major versions.

One cluster would be simpler to manage. The down side is that there are
cluster(global) data e.g. roles that would be shared by all the
databases. There is also the fact that everything is in one cluster and
should it fail all six databases will be down. Running as separate
clusters would give you some redundancy.

thanks in advance.
regards!
--

--
Adrian Klaver
adrian.klaver@aklaver.com

Am 19.04.2018 um 19:57 schrieb Edmundo Robles:

I will use replication as simple backup.

please keep in mind, replication is not a backup. All logical errors on
the master (delete from table and forgot the where-condition) will
replicated to the standby.

Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Yes, you are right, the replication is not a backup ;) , actually i
backup database daily at 3:00 am but if database crash, the amount of
data is big! that is the reason i want to replicate to reduce the data
loss. By the way a few days ago a job partner did a delete with no where.

On Thu, Apr 19, 2018 at 1:33 PM, Andreas Kretschmer <andreas@a-kretschmer.de

wrote:

Am 19.04.2018 um 19:57 schrieb Edmundo Robles:

I will use replication as simple backup.

please keep in mind, replication is not a backup. All logical errors on
the master (delete from table and forgot the where-condition) will
replicated to the standby.

Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

--

Hi,

If you want to reduce the amount of data lost, you might want to
consider another type of backup :
https://www.postgresql.org/docs/current/static/continuous-archiving.html

That awsome tool can also help you with it : https://pgbackrest.org/

On 04/19/2018 08:48 PM, Edmundo Robles wrote:

Yes, you are right, the replication is not  a backup ;)  , actually 
 i backup  database  daily at  3:00 am but if database crash,  the
amount  of data is  big!  that is the reason i want to  replicate to
reduce  the data loss. By the way  a few days ago a job partner did a
delete with no where.

On Thu, Apr 19, 2018 at 1:33 PM, Andreas Kretschmer
<andreas@a-kretschmer.de <mailto:andreas@a-kretschmer.de>> wrote:

Am 19.04.2018 um 19:57 schrieb Edmundo Robles:

I will use replication as simple backup.

please keep in mind, replication is not a backup. All logical
errors on the master (delete from table and forgot the
where-condition) will replicated to the standby.

Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com <http://www.2ndQuadrant.com&gt;

--

Kind regards,

--
Stefan FERCOT
http://dalibo.com - http://dalibo.org

On Thu, Apr 19, 2018 at 8:48 PM, Edmundo Robles <edmundo@sw-argos.com> wrote:

Yes, you are right, the replication is not a backup ;) , actually i backup database daily at 3:00 am but if database crash, the amount of data is big! that is the reason i want to replicate to reduce the data loss. By the way a few days ago a job partner did a delete with no where.

Replication will happily replicate a delete with no where. Backup will
at least have the day starting point.

For this kind of things you need to do log-archiving + base backups.
With this you can use PITR to roll forward from the last full backup
to just before the delete.

Francisco Olarte.

I use Barman, and I am happy with it.

Just in case you want to try different tools,
https://github.com/dhamaniasad/awesome-postgres.

On Thu, Apr 19, 2018 at 2:48 PM, Edmundo Robles <edmundo@sw-argos.com>
wrote: