0

I need to setup/manage a replication (PostgreSQL built-in replication or by means of third-party solutions like Slony) between two PostgreSQL instances programmatically with Java. Is there any Java API for this? Thanks in advance.

Improve this question

1 Answer 1

Reset to default
3

Unless you're going to use a managed service like AWS RDS or Heroku Postgres, the only Java APIs for this are those that concern file access and process management. If you're using AWS RDS you can use the AWS Java SDK; for Heroku there may be something similar.

If you're working with stock directly-managed PostgreSQL instances you'll need to open/parse/modify/write any relevant config files, or use external tools to do so. For starting/stopping DBs you'll need to use the regular utilities like pg_ctl via process invocation. If you need to work remotely on machines you'll probably want to use one of the Java SSH libraries.

So you'll be using things like ProcessBuilder, java.io.InputStreamReader, etc, possibly alongside remote shell tools like JSCH.

If you want to get fancy, you can use SSH as the transport between your master JVM and a JVM on each node, which is responsible for running processes and modifying files, and you can exchange serialized messages over the SSH channel for communication. While it's a bit more code to get it set up, this works extremely well and lets you "think in Java" to a greater degree than most other approaches.

Personally - I strongly suggest using Puppet, Chef, Ansible, etc to automate as much as possible, then just have your Java code invoke the automation tools. Also look into repmgr.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks Craig. So you mean, the only way seems to edit pgsql configuration files and/or invoking processes programmatically. Does it make sense to write and run a external program to get successfully completed transactions from master and execute the queries on the slave (with JDBC maybe) periodically? I dont know how to get transaction logs of pqsql instance programmatically anyways.
PostgreSQL 9.4 will support ALTER SYSTEM SET for SQL-level configuration, but you can only use it when the DB is running, so it's obviously not possible to use it to restart the database, for example. For the transaction logs you must let PostgreSQL invoke the archive_command when it's rotating logs - just write your own archive_command that sends the logs where you want. I strongly suspect you're biting off more work than you realise here, though, and suggest you re-examine options like AWS RDS if you want a simple, programmatically managed database.
This seems very complicated. Unfortunately there is no internet access in the production environment, so AWS RDS is not an option. I shall hold on to the other ways you suggested. Thanks Craig.
@Serdar Well, managing server instances across multiple machines is hardly going to be simple - at best, someone else will have already done the complicated work for you and provided a nice abstraction over the top. For this there isn't one yet. I suggest looking into Java remote management tools, you might find that someone's already built much of the framework so you just have to write the PostgreSQL-specifics.

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.