Infrastructure at your Service

Tag

PostgreSQL Archives - Blog dbi services

Daniel Westermann

Real time replication from Oracle to PostgreSQL using Data Replicator from DBPLUS

By | Database Administration & Monitoring | No Comments

I’ve done quite some real time logical replication projects in the past, either using Oracle Golden Gate or EDB replication server. Build in logical replication in PostgreSQL (which is available since PostgreSQL 10) can be used as well when both, the source and the target are PostgreSQL instances. While being at the DOAG conference and exhibition 2019 I got in contact with people from DBPLUS and they provide a product which is called “Data Replicator”….

Read More
Daniel Westermann

Enabling, disabling, and validating foreign key constraints in PostgreSQL

By | Database Administration & Monitoring | No Comments

Constraints are in important concept in every realtional database system and they guarantee the correctness of your data. While constraints are essentials there are situations when it is required to disable or drop them temporarily. The reason could be performance related because it is faster to validate the constraints at once after a data load. The reason could also be, that you need to load data and you do not know if the data is…

Read More
Daniel Westermann

A schema and a user are not the same in PostgreSQL

By | Database Administration & Monitoring | One Comment

When people with an Oracle background attend our PostgreSQL DBA Essentials training there is always a bit of confusion about schemas and users. In Oracle a schema and a user is a one to one relationship and there is no real distinction between a user and a schema. In PostgreSQL the situation is different: All the objects a user is creating are created in a specific schema (or namespace). Other users may or may not…

Read More
Daniel Westermann

Dealing with corrupted system indexes in PostgreSQL

By | Database Administration & Monitoring | No Comments

This is something you do not want to see on an important PostgreSQL system: [email protected]:/home/postgres/ [pgdev] psql psql: error: could not connect to server: FATAL: index “pg_class_oid_index” contains unexpected zero page at block 0 HINT: Please REINDEX it. The hint is pretty clear on how you should fix this, but anyway, lets do a short demo on how to do it.

Read More
Daniel Westermann

Can there be orphaned data files in PostgreSQL?

By | Database Administration & Monitoring | No Comments

PostgreSQL, as all other relational database systems, needs to persist data on disk, either by writing the writing the write ahead log or by synchronization the data files on disk whenever there is a checkpoint. When it comes to the data files PostgreSQL creates a new one once a relation reaches segment_site, which is 1GB by default. Because of this a relation can consist of many files if the relation is growing. The question I…

Read More
Daniel Westermann

PostgreSQL 13 will allow to drop databases even when there are connections

By | Database Administration & Monitoring | No Comments

One of the most popular blogs I’ve written about PostgreSQL was about the three databases that PostgreSQL creates by default (well, actually it is initdb that creates the databases). One of the points mentioned in that post was, that you can not drop a database as long as there are sessions against the database you want to drop. This will change with PostgreSQL 13, which is currently in development.

Read More
Julia Gugel

Connection pooling with PgBouncer

By | Database Administration & Monitoring | No Comments

Some of you may know the case: As soon as the number of users grow, the number of resource problems increases. Have you ever thought about using a connection pooler? Too complex, too much administration effort? In this post I like to explain, how the connection pooler can help you with your memory, as well as showing you the simplicity of setup connection pooling with PgBouncer.

Read More