Infrastructure at your Service

Category

Database Administration & Monitoring

Daniel Westermann

Be careful with prepared transactions in PostgreSQL

By | Database Administration & Monitoring | No Comments

PostgreSQL gives you the possibility for two-phase commit. You’ll might need that if you want an atomic distributed commit. If you check the PostgreSQL documentation there is a clear warning about using these kind of transactions: “Unless you’re writing a transaction manager, you probably shouldn’t be using PREPARE TRANSACTION”. If you really need to use them, you need to be very careful, that prepared transactions are committed or rollback-ed as soon as possible. In other…

Read More
Jérôme Dubar

Oracle Database Appliance: what have you missed since X3/X4/X5?

By | Database Administration & Monitoring, Database management, Hardware & Storage, Oracle | No Comments

Introduction ODA started to become popular with X3-2 and X4-2 in 2013/2014. These 2 ODAs were very similar. The X5-2 from 2015 was different with 3.5 inches disks instead of 2.5 inches and additional SSDs for small databases (FLASH diskgroup). All these 3 ODAs were running 11gR2 and 12cR1 databases and were managed by the oakcli binary. If you’re still using these old machines, you should know that there is a lot of differences compared…

Read More
Steven Naudet

SQL Server: Control the size of your Transaction Log file with Resumable Index Rebuild

By | Database Administration & Monitoring, SQL Server | 2 Comments

Introduction In this blog post, I will demonstrate how the Resumable capability of Online index rebuild operation can help you to keep the transaction log file size under control. An index rebuild operation is done in a single transaction that can require a significant log space. When doing a Rebuild on a large index the transaction log file can grow until your run out of disk space. On failure, the transaction needs to rollback. You…

Read More
Daniel Westermann

[Data]nymizer – Data anonymizer for PostgreSQL

By | Database Administration & Monitoring | No Comments

Often there is the requirement to populate a test or development database with data from production, but this comes with a risk: Do you really want, that developers or testers have access to sensitive data? In a lot of companies this might not be an issue, but for others, sensitive data must not be available to any other database than production. In Oracle there is Data Masking but there is nothing in Community PostgreSQL which…

Read More
Daniel Westermann

8000 PostgreSQL clusters on one host?

By | AWS, Database Administration & Monitoring | No Comments

Coming back to the last post: 8000 databases in one PostgreSQL cluster?. As we’ve seen this is doable but comes with a lot of pain points. Another solution for this kind of requirement would be to host 8000 PostgreSQL clusters on one host. Again, I am not saying this is something you should do, and again, this is not how it finally will be implemented. This is more about if you can do it, and…

Read More
Daniel Westermann

PostgreSQL on FreeBSD, installation from source code

By | Database Administration & Monitoring | No Comments

This is the next post in this little series about PostgreSQL on FreeBSD. You can find the previous one here: PostgreSQL on FreeBSD, getting started PostgreSQL on FreeBSD, updating & configuration PostgreSQL on FreeBSD, installation from packages The downside with installing from packages on FreeBSD is, that you can only run one version of PostgreSQL. If you want to run more than one, you need to install from source code, and this is the topic…

Read More
Daniel Westermann

PostgreSQL on FreeBSD, updating & configuration

By | Database Administration & Monitoring | No Comments

In the last post we installed FreeBSD, configured the keyboard, time zone and the first network interface, which gets it’s IP address via DHCP. In this post we’ll look at how we can configure the second interface, which gets a static IP address in the host only network. Once we’ve done that, we’ll update the system to the latest packages, have a look at the file system layout and kernel parameters so we can later…

Read More
Franck Pachot

Grafana, SQL and IN() list for multi-value variable

By | Database Administration & Monitoring | No Comments

By Franck Pachot . I’ve recently looked at Grafana. Pros: it is really nice, I mean aesthetically. If you want to build a dashboard for your boss, or put it on a large screen in the open space, that’s beautiful. Cons: not mature. I’m working for years with technology that works. When I have a problem, I find the solution (blog posts, forums, mailing lists, support notes)… But with modern software, the first problems I…

Read More
Daniel Westermann

PostgreSQL on FreeBSD, getting started

By | Database Administration & Monitoring | No Comments

Although we are running PostgreSQL mostly on Linux distributions, you can install an run PostgreSQL on a lot of other operating systems. On of them is FreeBSD. FreeBSD is one of those Unixes that derived from the Berkeley Software Distribution (BSD) and it is well known for having very few security issues and it is running a wide range of websites today. In addition, it is the foundation of storage appliances such as Isilon. Running…

Read More