Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A list of PostgreSQL libraries, tools and resources

github.com

Post reply on HN
Print view View original

11–20 of 30 posts

Re: A list of PostgreSQL libraries, tools and resources

#11

Post by AlterEgo20 »

Cieplak wrote: post #5

What is the best way to ETL data from one Postgres cluster to another (by ETL I mean things like data sanitization and denormalization)? a) I think most people end up doing some kind of batch synchronization, but I'm interested in streaming solutions. b) A lot of folks use trigger based replication, but triggers have to be on the primary/master node, and not just on the replicas. c) Another common solution is to forc…

Google "Postgres BDR". It is new, but it allows you to replicate changes on master to another master. With some configuration it can even change replicated data.

Re: A list of PostgreSQL libraries, tools and resources

#13

Post by Cieplak »

AlterEgo20 wrote: post #11
Cieplak wrote: post #5

What is the best way to ETL data from one Postgres cluster to another (by ETL I mean things like data sanitization and denormalization)? a) I think most people end up doing some kind of batch synchronization, but I'm interested in streaming solutions. b) A lot of folks use trigger based replication, but triggers have to be on the primary/master node, and not just on the replicas. c) Another common solution is to forc…

Google "Postgres BDR". It is new, but it allows you to replicate changes on master to another master. With some configuration it can even change replicated data.

Thank you. I believe that bidirectional replication will solve the ETL problem, if it can be configured to stream from a replica in the source/oltp cluster (to minimize network IO on the source cluster master db) to the master of the destination/olap cluster. The olap master can mirror the oltp data, but also use triggers to denormalize oltp tables.

One thing I'd like to see is the ability to sanitize/munge data from the source cluster before it is sent to the destination cluster, for masking sensitive data in the source cluster (PCI, HIPAA, etc).

Re: A list of PostgreSQL libraries, tools and resources

#15

Post by jbranchaud »

jbranchaud wrote: post #14

Does anyone know of sample PostgreSQL databases? Something akin to Northwind ( https://northwinddatabase.codeplex.com/ )?

I just found this list of sample databases -- https://wiki.postgresql.org/wiki/Sample_Databases

I'm not sure how good they are though.

Re: A list of PostgreSQL libraries, tools and resources

#16

Post by rMBP »

I found this talk by Christophe Pettus [1] very informative. The title is somewhat misleading as most of the talk has little to do with Python, but it's a good introduction to more advanced Postgres concepts. Also available in PDF form [2].

[1] https://www.youtube.com/watch?v=0uCxLCmzaG4

[2] http://thebuild.com/presentations/pycon-2014-pppp.pdf

Re: A list of PostgreSQL libraries, tools and resources

#17

Post by ellisv »

jbranchaud wrote: post #14

Does anyone know of sample PostgreSQL databases? Something akin to Northwind ( https://northwinddatabase.codeplex.com/ )?

Not sure if this is what you mean but PostgreSQL 9.5 introduced TABLESAMPLE. https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9....

Re: A list of PostgreSQL libraries, tools and resources

#18

Post by jbranchaud »

ellisv wrote: post #17
jbranchaud wrote: post #14

Does anyone know of sample PostgreSQL databases? Something akin to Northwind ( https://northwinddatabase.codeplex.com/ )?

Not sure if this is what you mean but PostgreSQL 9.5 introduced TABLESAMPLE. https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9....

Sorry, no, I'm meaning example databases with 'fake' or sample data.

Re: A list of PostgreSQL libraries, tools and resources

#19

Post by fphilipe »

pilif wrote: post #3

With regards to psql2csv: The default psql can already do this very nicely. Just use \copy. \copy (select whatever from whatever) to 'yourlocalfile.csv' with (format 'csv') and if you want column headers, add a "header true" inside of the with clause. Generally, \copy works just like COPY[1] but it does so from the remote server to the local machine, whereas file names given to COPY are relative to the server. Yes. A…

psql2csv is just a wrapper around what you're describing. The point is that you don't have to write the "boilerplate" when all you need is a single query. Plus, it works nice with stdin and stdout, making it handy for queries stored in a file, or for piping the CSV to another program.

Re: A list of PostgreSQL libraries, tools and resources

#20

Post by beermann »

Cieplak wrote: post #5

What is the best way to ETL data from one Postgres cluster to another (by ETL I mean things like data sanitization and denormalization)? a) I think most people end up doing some kind of batch synchronization, but I'm interested in streaming solutions. b) A lot of folks use trigger based replication, but triggers have to be on the primary/master node, and not just on the replicas. c) Another common solution is to forc…

Bucardo does this and the setup it a lot easier than most tools. I'm currently using it to migrate a live postgres database to another location but it can perform ETL as well.

EDIT: doesn't really address your point b) however.

Post reply on HN