Infrastructure at your Service

PostgreSQL Archives - Page 3 of 20 - Blog dbi services

Daniel Westermann

Are statistics immediately available after creating a table or an index in PostgreSQL?

By | Database Administration & Monitoring | No Comments

While giving the last PostgreSQL DBA Essentials workshop this question came up: When we create a table or an index: are the statistics available automatically? To be more precise: When we create and load a table in one step, create an index on that table afterwards: Do we have the statistics available by default or do we need to wait for autovacuum to kick in or analyze manually? Lets see …  

 
Read More
Pierre Sicot

DBVISIT from Oracle to Postgres

By | Database Administration & Monitoring | No Comments

As I regularly work on Oracle and PostgreSQL, I decided to test the replication from Oracle to PostgreSQL using the Dbvisit Replicate tool. Dbivisit Replicate does not use Oracle logminer ot triggers but its own mining processes to get the changes when they are written to the redo logs. When a change appears in the redo log, an external file called PLOG is generated and transferred to the target. The architecture is quite easy to…

 
Read More
Daniel Westermann

Can I do it with PostgreSQL? – 18 – Instead of triggers on views

By | Database Administration & Monitoring | No Comments

It has been quite a while since the last post in this series but today comes the next one. Being at a customer this morning this question popped up: Can we have instead of triggers on a view in PostgreSQL as well? I couln’d immediately answer (although I was quite sure you can) so here is the test. I took an example for Oracle from here and re-wrote it in PostgreSQL syntax.  

 
Read More
Daniel Westermann

Auto pre-warming in EDB Postgres Advanced Server 10

By | Database Administration & Monitoring | No Comments

Some days ago EDB Postgres Advanced Server 10 was released and one feature which might be handy is auto pre-warming. What this does is to save all the buffers (or better a description of the buffers) which are currently loaded in to shared_buffers to disk and then re-read the buffers automatically when the instance is restarted. Lets see how it works.  

 
Read More
Daniel Westermann

Displaying the contents of a PostgreSQL data file with pg_filedump

By | Database Administration & Monitoring | No Comments

Did you ever wonder what exactly is in a PostgreSQL data file? Usually you don’t care, I agree. But there might be situations where knowing how you can do this might be a great help. Maybe your file is corrupted and you want to recover as much data as possible? Maybe you just want to do some research. There is a utility called pg_filedump which makes this pretty easy. Lets go …  

 
Read More
Daniel Westermann

Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 logical replication?

By | Database Administration & Monitoring | No Comments

When you start using PostgreSQL 10 logical replication you might think it is a good idea to setup bi-directional replication so you end up with two or more masters that are all writable. I will not go into the details of multi master replication here (conflict resolution, …) but will show what happens when you try to do that. Lets go …  

 
Read More