0

Hi i am able to start streaming replication with postgresql, copying the database files with doing theis command: 

select pg_start_backup('clone', true);

That's right, but the thing is that i need to copy all the database files in /var/lib/postgresql/data to one server to another. That files are really big because of indexes for example.

I want to know if it is possible to do the same using a simple dump and restoring it on the slave, what is smaller and faster to transfer in the network, it will work? And what should i do to work?

Improve this question
6
  • No, I don't think so. Commented Dec 19, 2017 at 11:53
  • Thank you is there a fast way to start postgresql replication. I remember how fast is to do that with mysql, obvious that postgresql is far better (for unknow reason?) in speed for our application but.... Commented Dec 19, 2017 at 12:04
  • 2
    Mysql has a lot of different types of replication so you are probably comparing apples to oranges. pg_basebackup is the easiest way, but yes, it will copy all the database files — there is no way round that for physical replication. Commented Dec 19, 2017 at 12:32
  • 1
    pg_basebackup is the way to go. Logically it is the same as pg_dumpall and will be much faster as the "import" step isn't necessary Commented Dec 19, 2017 at 12:39
  • dump restore is mostly slower than pg_basebackup when copying all databases in a cluster Commented Dec 20, 2017 at 8:46

1 Answer 1

Reset to default
2

You cannot start streaming replication without a physical copy of the data directory; usually created with the pg_basebackup command.

The reason for this has to do with what streaming replication and WAL shipping replication do. In both cases you are applying the Write Ahead Log to the data directory using postgresql's recovery mechanism. So in order to not break database consistency you must be applying the same change set to the same starting point and you are restricted to replicating the entire database cluster. This is also why you cannot interrupt streaming or drop WAL segments, since once you have broken continuity the database server cannot guarantee that it is in a consistent state.

In postgreSQL 10 or later you could use logical replication to replicate only one database in a cluster or even a single table. And in that case you can start with a "logical copy" of the database such as are produced by pg_dump.

Improve this answer
1
  • The logical replication is exactly what i really need. When possible, we will migrate to PostgreSQL 10 and will stop the streaming replication in favor of logical replication. Commented Dec 26, 2017 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.