Questions[database-replication](server)

I have 3 servers with Ubuntu 19.10, PostgreSQL 12.2 and Patroni 1.6.4 and I would like to have the following setup:

I want to have two servers with synchronised replication and I want to add a 3rd server with pglogical replication just to accept inserts to specific tables. no DELETE, TRUNCATE or anything else.

I log a huge amount of purchases per day, around 300M. I want to have to servers to already have the purchases history of the last 30 days, and the 3rd server which has a really big storage, will hold all the history of the purchases.

So far I have a patroni cluster with etcd for the 2 servers with the following configuration:

This is for the first and 2nd server I have the following configuration, of course the name of the server differs in each configuration:

scope: patroni_cluster_1
name: server_X

restapi:
  listen: 0.0.0.0:8008
  connect_address: X.X.X.X:8008

etcd:
  hosts: X.X.X.X:2379
  protocol: http

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout : 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_keep_segments: 100

  initdb:
  - encoding: UTF8
  - data-checksums

  pg_hba:
  - host replication replicator 0.0.0.0/0 md5
  - host all all 0.0.0.0/0 md5

postgresql:
  listen: 0.0.0.0:5432
  connect_address: X.X.X.X:5432
  synchronous_mode: true
  synchronous_mode_strict: false
  data_dir: /var/lib/postgresql/patroni_cluster_1/server_X/data
  bin_dir: /usr/lib/postgresql/12/bin
  authentication:
    replication:
      username: replicator
      password: XXXX
    superuser:
      username: postgres
      password: XXXX
  parameters:
    shared_preload_libraries: pglogical

I added pglogical as a shared library because of the 3rd server which I would like to know if I can fully setup with Patroni.

I read about how to setup pglogical with https://blog.dbi-services.com/postgresql-logical-replication-with-pglogical/ and I would like to know if I can fully configure the 3rd server with pglogical for inserts only.

I'm new to Patroni and I'm pretty lost so any information regarding this issue would be greatly appreciated.

I'm looking into PostgreSQL replication solutions. I know two varieties of these solutions

1. Low level - which involves streaming log-shipping, hot-standby features available in PostgreSQL 9.x
2. High level - Slony, Londiste that work at SQL query level

My database is not very busy and huge (at least now). But I want to avoid downtimes due to Amazon EC2 failures (like the one that happened recently). My solution is to maintain a slave in a different Availability zone that will remain in sync with my master database instance. That way I can failover to it when master goes down. One point to consider is that, this will require continuous replication of data from master to slave, which will be network traffic across EC2 availability zones. It's not free. It costs 1 cent per GB right now, but after reading some calculations in the PostgreSQL cookbook, I learnt that costs can grow very high even if DB traffic is low. For instance in the "Hot physical backup and continuous archiving" chapter of the "PostgreSQL 9 Administration Cookbook" I read this:

If archive_timeout is set to 30 seconds, we will generate a minimum of 2*60*24 = 2880 files per day, each 16 MB in size, so a total volume of 46 GB per day (minimum)

[and that I assume with minimum traffic on the DB]

My only requirement is that, each write SQL query that executes on the Master server, should be replayed on the Slave server. If this is done on event callback, then that will be perfect because there will be data transfer between master and slave only when DB is being modified and not every 30 seconds or so even if no changes to DB have occured.

Therefore I thought Londiste might be the solution for me, but I am not 100% sure that it works that way.

What do you suggest?

I've been developing an app w/ server infrastructure for a while now, and I'm getting ready to release soon(ish).

So far the server backend has been running on PostgreSQL 8.4.7, and it's working as it should.

Right now, though, I'm thinking about upgrading to 9.0.X to be on a more recent release for the beta testing (and subsequent release), and to make it easier to set up database replication in the future.

My questions:

• Is PostgreSQL 9.0.4 a viable DB to use as of now? Are there any known major bugs?

• Are there any noticeable performance differences?

• Is replication (async ok, sync preferred) easier to set up in 9.0.X than in 8.4.X?

My websites have been growing in terms of traffic and the load on the mysql has been increasing. I wanted a solution that would help deal with the increased load on mysql (all queries already optimized) because of the increase in traffic + backup servers that could serve as a failover if my main server fails.

I have read about setting up multiple web and database servers, but had a few questions:

1) How do you set up multiple web servers while maintaining the same content (would I have to upload the files to all the servers everytime they were updated or is there another way?)

2) Similarly, if I had to scale out the DB into more than one server, is there any other way apart from replication or is mysql replication the best way to go about it.

3) I have read that separating the database from the web server is a good idea, why is that? If I had 2 servers, can't I have both the DB and the files on both servers.

4) Is something known as a load balancer needed and would it help balance mysql queries as well if replication were set up?

Just very confused, would like some help.

I am developing a web apps, this web application will be using MySQL.

Now I need to replicate my client's Oracle database into MySQL, only a few tables will be involved.. a table can be up to 2-3 million rows.

I only have SELECT privilege on this Oracle, so don't ask me to install any kind of service on the Oracle machine. I have complete control on the MySQL side however.

The replication is only one way (Oracle to MySQL). I can write a simple script to truncate MySQL table and repopulate it every night but I think this is very inefficient, there must be a better way.

Is there any free tools I can use? Expensive database replication system is definitely out of the question.