CODEWITHSUNDEEP
postgresqlreplication
Gershom Maes
Gershom Maes

Reputation: 8170

Postgres replication with referenced files

I have a postgres database which contains references to files on the filesystem. These references are simple VARCHAR values, so I understand there is no referential integrity guaranteed:

CREATE TABLE user (
    username VARCHAR(50) PRIMARY KEY,
    age INTEGER,
    template VARCHAR(100) -- Filepath of a file on disk
);

INSERT INTO
    user   (username,   age,   template)
    VALUES ('bob',      32,    '/templates/cool-template.html');

This is fine, the lack of referential integrity is not a concern. But now I go and set up standard streaming replication (I follow the tutorial on this page under the "How To Use" heading).

What is the best way for me to replicate any filesystem files (e.g. "cool-template.html") along with postgres db content? I don't want to store files in the database. I am already aware I can independently rsync any files, but is there a better way? Does postgres provide some sort of hook which could trigger a file to be backed up?

Thanks for any help!

Upvotes: 0

Views: 61

Answers (1)

hugoShaka
hugoShaka

Reputation: 5467

The simplest solution as you said is to use a dummy rsync (triggered via cron or a hook), just make sure you're not only syncing existing files (if so you'll sever sync the deletion of a file).

rsync -avh source/ dest/ --delete

You can also use a amazon or google bucket to store your data (it won't be replicated but this is still a pretty safe and highly available solution).

If you're confident enough you can play with DRBD and Ceph to create a storage cluster (not advised unless you really know what you're doing and you have more than two nodes).

I'd personally go for amazon or google shared storage : low risk, not much maintenance, simple to understand and use.

Upvotes: 1

Related Questions