Articles

Backup PostgreSQL to AWS S3

Published on Jun. 28, 2023 by Gabriel Bordeaux

Introduction Backup PostgreSQL to AWS S3 step by step Install awscli Install AWS CLI: pip install awscli Configure your credentials: aws configure Clone backup tool ...

Read More

Install PostgreSQL 15 on Debian/Ubuntu servers

Published on Jun. 22, 2023 by Gabriel Bordeaux

Introduction Install PostgreSQL 15 step by step Add the PostgreSQL repository to your sources echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list Import the repository ...

Read More

Easy migration from Travis to GitHub Actions

Published on Jan. 5, 2022 by Gabriel Bordeaux

Introduction As of 2021, Travis has stopped giving access to its CI resources to open source projects for free. GitHub Actions is a free alternative ...

Read More

Encrypt a file for GitHub Actions

Published on Dec. 18, 2021 by Gabriel Bordeaux

Introduction You can encrypt files containing secrets that you want to use in GitHub Actions with GPG and decode them with a passphrase stored securely ...

Read More

Upgrade to PostgreSQL 12

Published on Nov. 19, 2019 by Gabriel Bordeaux

Introduction Upgrading to PostgreSQL 12 is a simple procedure requiring minimum downtime. The process consists of 3 steps: Installing PostgreSQL 12 without a new cluster ...

Read More

Easy replication setup with PostgreSQL 12

Published on Nov. 5, 2019 by Gabriel Bordeaux

Changes from older versions recovery.conf is not used anymore to setup the replication standby_mode parameter has been replaced with 2 files: standby.signal and recovery.signal Create ...

Read More

How to serve a Flask application with uWSGI and Nginx

Published on Dec. 19, 2018 by Gabriel Bordeaux

Introduction This is a simple introduction on how to serve a Python Flask application with uWSGI and Nginx on Ubuntu. Prerequisite Install Nginx and Python: ...

Read More

Connect to PostgreSQL over SSL/TLS on Debian/Ubuntu

Published on Jun. 7, 2018 by Gabriel Bordeaux

Introduction This is a simple introduction on how-to connect to PostgreSQL from a remote server using a self signed SSL certificate. Prerequisite PostgreSQL is installed ...

Read More

Configure SSL/TLS for MySQL on Debian/Ubuntu

Published on May. 31, 2018 by Gabriel Bordeaux

Introduction This article is a simple how-to on configuring MySQL to accept remote connections with SSL/TLS encryption. Install packages on the MySQL server apt-get update ...

Read More

PostgreSQL: querying Bigquery using bigquery_fdw foreign data wrapper

Published on May. 24, 2018 by Gabriel Bordeaux

Introduction This article is a step by step how-to to query BigQuery from a PostgreSQL instance using the foreign data wrapper bigquery_fdw. Prerequisites: PostgreSQL >= ...

Read More

PostgreSQL foreign data wrapper: Aggregate Pushdown

Published on May. 17, 2018 by Gabriel Bordeaux

Introduction PostgreSQL 10 implemented push aggregate functions to the remote server to improve foreign data wrapper performances (see release notes) Without aggregate pushdown, when an ...

Read More

PostgreSQL foreign data wrapper with postgres_fdw

Published on May. 10, 2018 by Gabriel Bordeaux

Setting up postgres_fdw Using postgres_fdw requires 4 steps: Creating the extension Creating the server Creating a user mapping Importing the tables Before setting up the ...

Read More

Migrate to PostgreSQL 9.6 with pg_upgrade

Published on Nov. 20, 2017 by Gabriel Bordeaux

Introduction You can migrate from a version of PostgreSQL to another with pg_dump (see my article on how to migrate from 9.4 ro 9.5 as ...

Read More

Migrate to PostgreSQL 10 with pg_upgrade

Published on Nov. 9, 2017 by Gabriel Bordeaux

Introduction You can migrate from a version of PostgreSQL to another with pg_dump or with pg_upgrade. pg_upgrade allows you to migrate with or without a ...

Read More

Install PostgreSQL 10 on Debian/Ubuntu servers

Published on Oct. 5, 2017 by Gabriel Bordeaux

Introduction PostgreSQL 10 was released on 05/10/2017. Install PostgreSQL 10 step by step Add the PostgreSQL repository to your sources echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg ...

Read More

GCE BigQuery vs AWS Redshift vs AWS Athena

Published on Jul. 20, 2017 by Gabriel Bordeaux

Updates 7/20/17 update Following Tino Tereshko's advice (he is Big Data Lead at Google Cloud Office of CTO), I added the metrics for BigQuery standard ...

Read More

Easy replication setup with PostgreSQL

Published on Jul. 19, 2017 by Gabriel Bordeaux

Introduction Replicating a PostgreSQL server to a slave is an easy process with PostgreSQL. The documentation below explains how to create a new database slave ...

Read More

Setup a cascading PostgreSQL replication in a few easy steps

Published on Jul. 19, 2017 by Gabriel Bordeaux

Introduction Setting up a cascading replication means to create a downstream slave from an existing master-slave setup. If you want to create a regular PostgreSQL ...

Read More

Migrate from PostgreSQL 9.4 to 9.5 with easy steps

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction PostgreSQL 9.5 was released on 01/07/2016. A list of new and upgraded features is available on the official documentation. The guide below provides a ...

Read More

Check if you have an Index on your IDs in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Why? PostgreSQL does not automatically create an index for your ID column. This article will explain why you should always create an Index on your ...

Read More

Group by a range of values with PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Here is a simple solution on how to group data within a range. As a basic example, you may have a list of team ...

Read More

Migrate to PHP7 with Nginx + PHP-FPM

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction PHP 7 has been recently released. It offers new features and benchmarks that show a decrease in memory consumption and great improvements in response ...

Read More

How to do ORDER BY RANDOM() on large tables?

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Getting a random row from a PostgreSQL table has numerous use cases. To process an instruction like "ORDER BY RANDOM()", PostgreSQL has to fetch ...

Read More

Store IP addresses in PostgreSQL with IP4R

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction IP4R is a PostgreSQL extension which supports 6 data types allowing you to store IPv4 & IPv6 addresses in a PostgreSQL table. As IP4R ...

Read More

Install PostgreSQL 9.5 on Debian/Ubuntu servers

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction PostgreSQL 9.5 was released on 01/07/2016. More information about the release is available on the official PostgreSQL documentation. Install PostgreSQL 9.5 step by step ...

Read More

Easy migrating from PostgreSQL 9.3 to 9.4 for Debian servers

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction PostgreSQL 9.4 was released on 12/18/2014. More information about the release is available on the official PostgreSQL documentation. This article is written to provide ...

Read More

PostgreSQL: Search dates on large tables with pg_IdFromDate()

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction pg_IdFromDate() is a small set of PostgreSQL stored procedures allowing you to search a row based on a date or a timestamp in a ...

Read More

Weighted ntile with PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction The PostgreSQL window function ntile is a ranking function dedicated to split data into a specified number of groups. It can allow you to ...

Read More

10 tips and tricks for PostgreSQL (part 1)

Published on Mar. 27, 2017 by Gabriel Bordeaux

Index Here are a few tips and tricks for PostgreSQL I have been using for some time. I invite you to have a look at ...

Read More

10 tips and tricks for PostgreSQL (part 2)

Published on Mar. 27, 2017 by Gabriel Bordeaux

Index This article is the 2nd part of my "10 tips and tricks for PostgreSQL". If you did not read the first part yet, you ...

Read More

ORDER BY [my custom order] in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Below you will find a little tutorial on how to sort data in a custom order. For example, if you have a list of ...

Read More

Yield management: How to maximize your profit and minimize your customer's satisfaction rate

Published on Mar. 27, 2017 by Gabriel Bordeaux

Yield what? The concept Let's use an airplane as an example. On a plane, there are 100 seats. A company will start selling tickets 3 ...

Read More

PostgreSQL's subqueries: multiple columns, better and faster results

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Subqueries are used every day by most developers and DBAs. We will present below several solutions to write the same subquery and get the ...

Read More

Introduction to JSON support in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Since the version 9.2, PostgreSQL supports the data type JSON. You can manipulate JSON in PostgreSQL with several functions and operators. Here is a ...

Read More

Improve the quality of your database using Foreign Keys in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction A foreign key is a constraint that specifies that the value of a column (or a group of columns) must match the values of ...

Read More

Average ignoring extremes (outliers) with PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction In some cases, you may want a "trimmed average" instead of a regular average. For example, if you try to calculate the average net ...

Read More

Introduction to GPG

Published on Mar. 27, 2017 by Gabriel Bordeaux

What is GPG? GPG is a GPL alternative to PGP. It is a powerful encryption tool created by Werner Koch. You can read more about ...

Read More

Window Functions in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Window functions looks at "windows" of your data while processing it. They give you "the ability to perform calculations across sets of rows that ...

Read More
RSS