Infrastructure at your Service

PostgreSQL Archives - Blog dbi services

Daniel Westermann

Can I do it with PostgreSQL? – 11 – NLS_DATE_FORMAT

By | Database Administration & Monitoring | No Comments

Following the last post about time zones this post is about the display and default format of date and time values. What people usually do to get a consistent display of date and time values in Oracle is to set NLS_DATE_FORMAT: SQL> show parameter nls_date_format; NAME TYPE VALUE ———————————— ———– —————————— nls_date_format string YYYY-MON-DD HH24:MI:SS SQL> alter session set nls_date_format=’DD.MM.YYYY HH24:MI:SS'; Session altered. SQL> select sysdate from dual; SYSDATE ——————- 29.03.2017 16:26:11 SQL> alter session…

 
Read More
Daniel Westermann

Can I do it with PostgreSQL? – 10 – Timezones

By | Database Administration & Monitoring | No Comments

This post is inspired by a question we received from a customer: In Oracle there is the sessiontimezone which returns the time zone of the session. Asking for the time zone of the session in Oracle returns you the offset to the UTC time: SQL> select sessiontimezone from dual; SESSIONTIMEZONE ————————————————————————— +02:00 This is fine as I am based in Switzerland and we skipped one hour in the night from last Saturday to Sunday  

 
Read More
Daniel Westermann

Vertically scale your PostgreSQL infrastructure with pgpool – 3 – Adding another standby

By | Database Administration & Monitoring | No Comments

In the first and second post in this series we did the basic pgpool setup including the watchdog configuration and then did a simple setup for automatically failover from a PostgreSQL master instance in case it goes down for any reason. In addition we told pgpool how an old master instance can be reconfigured as a new standby instance that follows the new master. In this post we’ll add another standby instance and then teach…

 
Read More
Daniel Westermann

Vertically scale your PostgreSQL infrastructure with pgpool – 2 – Automatic failover and reconfiguration

By | Database Administration & Monitoring | No Comments

In the first post of this little series we did the basic setup of pgpool and configured the watchdog. So, as of now, pgpool uses a VIP which is active on one of the pgpool nodes and failovers to the other node in case the node where the VIP currently runs on goes down for any reason. This provides the very same address for the application or clients to connect and eliminates the single point…

 
Read More
Daniel Westermann

Vertically scale your PostgreSQL infrastructure with pgpool – 1 – Basic setup and watchdog configuration

By | Database Administration & Monitoring | No Comments

I have written some posts on how you can make your PostgreSQL deployment high available by using PostgreSQL’s streaming replication feature in the past ( 1, 2 ). The main issue you’ll have to resolve with such a setup is how the application can be made aware of a new master when a fail over happened. You could use EDB Failover Manager (1, 2, 3, 4) for that because it provides the functionality to move…

 
Read More
Pierre Sicot

Postgres Barman and DMK

By | Database Administration & Monitoring | No Comments

As PostgreSQL is more and more present in our client’s infrastructure, I wanted to describe you the barman installation and configuration. Barman is the backup and recovery tool for PostgreSQL, I configured it using DMK out tool for infrastructure administrators on Oracle, MySQL, and PostgreSQL. I used two virtual severs running under RedHat Enterprise Libux 7.1, one for PostgreSQL database server (pg1) ands the second for barman (pg2). At first I install PostgreSQL 9.6 on…

 
Read More
Daniel Westermann

EDB BART 2.0 – How to upgrade and block level incremental backups

By | Database Administration & Monitoring | No Comments

We already have some posts on how you can use EDB BART to backup and restore your PostgreSQL instances from one central server (EnterpriseDB Backup and Recovery Tool (BART), getting started with postgres plus advanced server (2) – setting up a backup and recovery server). The current version you can download from the EnterpriseDB website is 1.1 but version 2.0 is in beta and can be tested already. The main new feature is that BART…

 
Read More
Daniel Westermann

Converting a column from one data type to another in PostgreSQL

By | Database Administration & Monitoring | 2 Comments

Last week at a customer the following question came up: We have a varchar2 column in Oracle that contains a date but actually is stored as a string. When we want to convert this column to be a real date data type in Oracle we can use dbms_redefinition. How can we do that in PostgreSQL? The first answer that came to me mind is: Add a new column (of data type date) and populate it…

 
Read More
Daniel Westermann

Can I do it with PostgreSQL? – 9 – Temporary tables

By | Database Administration & Monitoring | No Comments

It has been quite a while since the last posts in this series, so here is what we looked at until now: Can I do it with PostgreSQL? – 1 – Restore points Can I do it with PostgreSQL? – 2 – Dual Can I do it with PostgreSQL? – 3 – Tablespaces Can I do it with PostgreSQL? – 4 – External tables Can I do it with PostgreSQL? – 5 – Generating DDL…

 
Read More
Daniel Westermann

From PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime

By | Database Administration & Monitoring | No Comments

As you might know PostgreSQL 9.1 is out of support since last September. But, no surprise, there are still plenty of PostgreSQL 9.1 instances out there which need to be upgraded to a supported release. At one of our customers we exactly have this situation: The production environment is running on PostgreSQL 9.1.8 and needs to be upgraded to a recent release (9.5.5 in this case). To make life a little bit more fun there…

 
Read More