Backup PostgreSQL to AWS S3
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 ...
Articles
Install PostgreSQL 15 on Debian/Ubuntu servers
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 ...
Easy migration from Travis to GitHub Actions
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 ...
Encrypt a file for GitHub Actions
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 ...
Upgrade to PostgreSQL 12
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 ...
Easy replication setup with PostgreSQL 12
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 ...
How to serve a Flask application with uWSGI and Nginx
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: ...
Connect to PostgreSQL over SSL/TLS on Debian/Ubuntu
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 ...
Configure SSL/TLS for MySQL on Debian/Ubuntu
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 ...
PostgreSQL: querying Bigquery using bigquery_fdw foreign data wrapper
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 >= ...
PostgreSQL foreign data wrapper: Aggregate Pushdown
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 ...
PostgreSQL foreign data wrapper with postgres_fdw
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 ...
Migrate to PostgreSQL 9.6 with pg_upgrade
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 ...
Migrate to PostgreSQL 10 with pg_upgrade
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 ...
Install PostgreSQL 10 on Debian/Ubuntu servers
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 ...
GCE BigQuery vs AWS Redshift vs AWS Athena
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 ...
Easy replication setup with PostgreSQL
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 ...
Setup a cascading PostgreSQL replication in a few easy steps
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 ...
Migrate from PostgreSQL 9.4 to 9.5 with easy steps
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 ...
Check if you have an Index on your IDs in PostgreSQL
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 ...
Group by a range of values with PostgreSQL
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 ...
Migrate to PHP7 with Nginx + PHP-FPM
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 ...
How to do ORDER BY RANDOM() on large tables?
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 ...
Store IP addresses in PostgreSQL with IP4R
Introduction IP4R is a PostgreSQL extension which supports 6 data types allowing you to store IPv4 & IPv6 addresses in a PostgreSQL table. As IP4R ...
Install PostgreSQL 9.5 on Debian/Ubuntu servers
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 ...
Easy migrating from PostgreSQL 9.3 to 9.4 for Debian servers
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 ...
PostgreSQL: Search dates on large tables with pg_IdFromDate()
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 ...
Weighted ntile with PostgreSQL
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 ...
10 tips and tricks for PostgreSQL (part 1)
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 ...
10 tips and tricks for PostgreSQL (part 2)
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 ...
ORDER BY [my custom order] in PostgreSQL
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 ...
Yield management: How to maximize your profit and minimize your customer's satisfaction rate
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 ...
PostgreSQL's subqueries: multiple columns, better and faster results
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 ...
Introduction to JSON support in PostgreSQL
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 ...
Improve the quality of your database using Foreign Keys in PostgreSQL
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 ...
Average ignoring extremes (outliers) with PostgreSQL
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 ...
Introduction to GPG
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 ...
Window Functions in PostgreSQL
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 ...