Tag: PostGreSQL

Categorias
Tropeçando

Tropeçando 96

Refinement Code Review

With the right environment, I can look a bit of code written six months ago, see some problems with how it's written, and quickly fix them. This may be because this code was flawed when it was written, or that changes in the code base since led to the code no longer being quite right. Whichever the cause, the important thing is to fix problems as soon as they start getting in our way. As soon as I have an understanding about the code that wasn't immediately apparent from reading it, I have the responsibility to (as Ward Cunningham so wonderfully said) take that understanding out of my head and put it into the code. That way the next reader won't have to work so hard.

After all, many problems that code reviews seek to remedy are problems that only become problems when the code is read in the future.

MONITORING REPLICATION: PG_STAT_REPLICATION

PostgreSQL replication (synchronous and asynchronous replication) is one of the most widespread features in the database community. Nowadays, people are building high-availability clusters or use replication to create read-only replicas to spread out the workload. What is important to note here is that if you are using replication, you must make sure that your clusters are properly monitored.

The purpose of this post is to explain some of the fundamentals, to make sure that your PostgreSQL clusters stay healthy.

POSTGRESQL: WHAT IS A CHECKPOINT?

Check some information about checkpoints, mix and max wal size, how they operate toghether and what they play on our day-to-day database workload.

Checkpoints are a core concept in PostgreSQL. However, many people don’t know what they actually are, nor do they understand how to tune checkpoints to reach maximum efficiency. This post will explain both checkpoints and checkpoint tuning, and will hopefully shed some light on these vital database internals.

Boos your user defined functions in PostgreSQL

Using the RDBMS only to store data is restricting the full potential of the database systems, which were designed for server-side processing and provide other options besides being a data container. Some of these options are stored procedures and functions that allow the user to write server-side code, using the principle of bringing computation to data, avoiding large datasets round trips and taking advantage of server resources. PostgreSQL allows programming inside the database since the beginning, with User Defined Functions (UDFs). These functions can be written in several languages like SQL, PL/pgsql, PL/Python, PL/Perl, and others. But the most common are the first two mentioned: SQL and PL/pgsql. However, there may be “anti-patterns” in your code within functions and they can affect performance. This blog will show the reader some simple tips, examples and explanations about increasing performance in server-side processing with User Defined Functions in PostgreSQL. It is also important to clarify that the intention of this post isn’t to discuss whether Business Logic should be placed, but only how you can take advantage of the resources of the database server.

The career-changing art of reading the docs

Don’t wait for knowledge to find you through years of inefficient trial and error. Go get it. And the most convenient, comprehensive place to grab it was there in front of you all along.

Read the docs.

Categorias
Tropeçando

Tropeçando 92

Mocking/stubbing the current Date in Jest tests

This post goes through multiple approaches to mocking, stubbing and spying on the date constructor using Jest.

Don't get stuck

A history about the importance of know your time and to not get stuck.

https://frontpagelinux.com/articles/guide-through-history-of-unix-linux-everything-you-need-to-know/

For this special history guide, we are going to take a trip back in time to see where the seed of Linux was planted — namely via the Unix systems of the early 1970s and how it has progressed through the modern day. Though most are completely unaware of the enormous impact that Unix-like operating systems have planted on our society, understanding its storied history can allow us to realize why the Unix model has lived on far longer and become more successful than any other operating system architecture (and philosophy) in existence.

tmpmail - A temporary email right from your terminal

tmpmail is a command line utility that allows you to create a temporary email address and receive emails to the temporary email address. It uses 1secmail's API to receive the emails.

EXPLAIN docs

After a lot of time looking at query plans, we’re still coming across new operation types, fields, and terminology. Many of these terms were tricky to look up and understand, so we decided to share descriptions and useful links for many of the most common in a glossary.

Categorias
Tropeçando

Tropeçando 91

Introducing the MDN Web Docs Front-end developer learning pathway

The MDN Web Docs Learning Area (LA) was first launched in 2015, with the aim of providing a useful counterpart to the regular MDN reference and guide material. MDN had traditionally been aimed at web professionals, but we were getting regular feedback that a lot of our audience found MDN too difficult to understand, and that it lacked coverage of basic topics.
https://developer.mozilla.org/en-US/docs/Learn

SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN

This is one of the most common questions asked by developers who write SQL queries against the PostgreSQL database. There are multiple ways in which a sub select or lookup can be framed in a SQL statement. PostgreSQL optimizer is very smart at optimizing queries, and many of the queries can be rewritten/transformed for better performance.

Expose

Expose is a beautiful, open source, tunnel application that allows you to share your local websites with others via the internet.

Combining event sourcing and stateful systems

As Brent stated: In this two-part series, my colleague Freek and I will discuss the architecture of a project we're working on. We will share our insights and answers to problems we encountered along the way. This part will be about the design of the system, while Freek's part will look at the concrete implementation.

Let's set the scene.

Does scrum ruin great engineers or are you doing it wrong?

A question on Stack Overflow’s Software Engineering site caught our attention recently. It tries to come to terms with the impact of scrum on developers' ability to do a great job. The claim is a bold one: Scrum is turning good developers into average ones. Could that be true?

Categorias
PostGreSQL

PostgreSQL – pg_upgrade from 10 to 12

I have some PostgreSQL databases running pretty well but we need to keep our software updated. This is a mandatory practice for a high-quality service. Those servers are running version 10 and they need to be upgraded to version 12. I have used pg_dump / pg_restore strategy for a long time, but this time I would rather use pg_upgrade.

Let's dive into how to do it.

Table of contents:

  1. Install
  2. InitDB
  3. Check upgrade consistency
  4. Set locale
  5. Upgrade
  6. Configurations

Install

The package postgresql12-server contains everything needed to run the server, but my databases use some extensions [1], then I will add postgresql12-devel and postgresql12-contrib to be able to compile and to install the extensions.

yum install postgresql12-server postgresql12-devel postgresql12-contrib

InitDB

After installation we need to setup new server with initdb:

~% /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database … OK

Check upgrade consistency

We need to check compatibility. Turn to postgres user (su - postgres) and run the command:

~% /usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin --new-bindir=/usr/pgsql-12/bin --old-datadir=/var/lib/pgsql/10/data --new-datadir=/var/lib/pgsql/12/data --check

Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               fatal

Your installation contains tables declared WITH OIDS, which is not supported
anymore. Consider removing the oid column using
    ALTER TABLE ... SET WITHOUT OIDS;
A list of tables with the problem is in the file:
    tables_with_oids.txt

Failure, exiting

As you may see, I got a fatal error, indicating that the upgrade is not possible. In my case, tables with OIDs are the culprit. In your case could be something else. In any case, we need to fix before upgrading.

I fixed tables removing OIDs on mentioned tables. And ran check again:

Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

Yay!

Set locale

There is a tricky configuration that is not detected by pg_upgrade check but it is very important to me. I use C locale on my databases [2], then I need to perform an extra step. If this is your case, you may follow the same steps applying yours.

I need to stop postgresql10 and start postgresql12:

systemctl stop postgresql-10.service
systemctl start postgresql-12.service

Then I run locale change at my template1 then locale will be enabled when my database will be upgraded.

UPDATE pg_database SET datcollate='C', datctype='C' WHERE datname='template1';

And stop again: systemctl stop postgresql-12.service to be ready to upgrade.

Upgrade

Upgrade command is the same that we run before, without --check flag.

~% /usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin --new-bindir=/usr/pgsql-12/bin --old-datadir=/var/lib/pgsql/10/data --new-datadir=/var/lib/pgsql/12/data

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

Consider running analyze_new_cluster. Optional but nice to have.

vacuumdb: processing database "mydb": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "mydb": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "mydb": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics

Done

Configurations

Before deleting your old cluster, remember to get some of your configurations.

mv /var/lib/pgsql/12/data/pg_hba.conf /var/lib/pgsql/12/data/pg_hba.conf.new
cp /var/lib/pgsql/10/data/pg_hba.conf /var/lib/pgsql/12/data/

I am not a big fan of writing directly to postgresql.conf file. Instead, I keep configuration files under version control and include the directory where those files are deployed. I treat them as code, then it becomes easier to maintain and manage.

Another advantage is that I don't have any mess about config file differences when a new version arises. I am automatically using new default configurations, my customized setting are loaded and I can quickly address any incompatibility caused by migration without touching the original conf file.

Let's go for it:

# Add settings for extensions here
include_dir = '/var/lib/pgsql/conf.d'   # include files ending in '.conf' from

Then you may delete your old cluster. 🙂

Links:

  1. Extensions: https://www.postgresql.org/docs/current/contrib.html
  2. Locale: https://www.postgresql.org/docs/current/locale.html
Categorias
Tropeçando

Tropeçando 89

Upgrading Postgres major versions using Logical Replication

Arch Fun Statistics

Everyday Hacks for Docker

In this post, I’ve decided to share with you some useful commands and tools I frequently use when working with awesome Docker technology. There is no particular order or “coolness level” for every “hack.” I will simply present the use case and how the specific command or tool has helped me with my work. Read these great hacks and make sure to check out the great hack of all – Codefresh –  the best CI for Docker out there.

https://codefresh.io/docker-tutorial/not-ignore-dockerignore-2/

A video course Introduction to CQRS and Event Sourcing

Categorias
Tropeçando

Tropeçando 88

Intro Guide to Dockerfile Best Practices

There are over one million Dockerfiles on GitHub today, but not all Dockerfiles are created equally. Efficiency is critical, and this blog series will cover five areas for Dockerfile best practices to help you write better Dockerfiles: incremental build time, image size, maintainability, security and repeatability. If you’re just beginning with Docker, this first blog post is for you! The next posts in the series will be more advanced.

The case against the ifsetor function

how to traverse nested array structures with potentially non-existing keys without throwing notices

Laravel Beyond CRUD

Proposal for thinking Laravel applications using DDD approach. A blog series for PHP developers working on larger-than-average Laravel projects.

Designing Your First App in Kubernetes, Part 1: Getting Started

Kubernetes’s gravity as the container orchestrator of choice continues to grow, and for good reason: It has the broadest capabilities of any container orchestrator available today. But all that power comes with a price; jumping into the cockpit of a state-of-the-art jet puts a lot of power under you, but how to actually fly the thing is not obvious.

How to run short ALTER TABLE without long locking concurrent queries

Categorias
Tropeçando

Tropeçando 85

Good Engineering Practices while Working Solo

How Much maintenance_work_mem Do I Need?

While I generally like PostgreSQL's documentation quite a bit, there are some areas where it is not nearly specific enough for users to understand what they need to do. The documentation for maintenance_work_mem is one of those places. It says, and I quote, "Larger settings might improve performance for vacuuming and for restoring database dumps," but that isn't really very much help, because if it might improve performance, it also might not improve performance, and you might like to know which is the case before deciding to raise the value, so that you don't waste memory. TL;DR: Try maintenance_work_mem = 1GB. Read on for more specific advice.

JSONPlaceholder

Fake Online REST API for Testing and Prototyping

A Beginner’s Guide to the True Order of SQL Operations

The SQL language is very intuitive. Until it isn’t. A guide to understanding the order of a SELECT operation.

The state of open source security - 2019

Snyk is an incredible tool for package security. And they released a state of open source security, talking about open source adoption and package, images and code vulnerabilites. We are talking about maven, npm, pypi, docker, etc.

Categorias
Tropeçando

Tropeçando 82

docz

It has never been so easy to document your things!

usql

A universal command-line interface for PostgreSQL, MySQL, Oracle Database, SQLite3, Microsoft SQL Server, and many other databases including NoSQL and non-relational databases!

Agendando tarefas com o Cron para Node

O Cron para Node é um pacote npm que nos permite fazer o agendamento de tarefas baseado em uma regra de tempo. Ele é baseado no Cron do Linux e seu funcionamento segue a mesma linha. Com ele é possível definir uma função para ser executada de tempos em tempos, ou seja, ela será agendada para ser executada dentro do Node. É uma maneira bastante eficaz para tarefas repetitivas que precisam rodar em segundo plano, como o envio de notificação, backup de banco de dados, entre outras.

Howto: use one VCL per domain

The Varnish Configuration Language (VCL), I'm sure you know already, is the source of Varnish versatility: by only enforcing the protocol flow and leaving the business logic to the user, Varnish can be easily configured to do things far beyond caching.

However, because the logic of websites is generally focused around hosts, and the VCL thinks in terms of processing steps, configuration may sometimes a bit odd, with the need to place safeguards around your code to ensure that logic for one host isn't applied to another one.

It works, but it can be tedious and unwieldy, so today we are going to have a look at how we can silo our VCL per website to achieve better maintainability.

Understanding the 8 Fallacies of Distributed Systems

Are you working on a distributed system? Microservices, Web APIs, SOA, web server, application server, database server, cache server, load balancer - if these describe components in your system's design, then the answer is yes. Distributed systems are comprised of many computers that coordinate to achieve a common goal.

More than 20 years ago Peter Deutsch and James Gosling defined the 8 fallacies of distributed computing. These are false assumptions that many developers make about distributed systems. These are usually proven wrong in the long run, leading to hard to fix bugs.

PostgreSQL Tuning: Key Things to Drive Performance

Performance is one of the key requirements in software architecture design, and has been the focus of PostgreSQL developers since its beginnings

Illuminate your career

If you are a developer, this article is for you.

5 Things You Have Never Done with a REST Specification

How to to Backup Linux with Snapshots

While working on different web projects I have accumulated a large pool of tools and services to facilitate the work of developers, system administrators and DevOps
One of the first challenges, that every developer faces at the end of each project is backup configuration and maintenance of media files, UGC, databases, application and servers' data (e.g. configuration files).

Awesome PHP

A curated list of amazingly awesome PHP libraries, resources and shiny things.

Categorias
Tropeçando

Tropeçando 81

ACME Support in Apache HTTP Server Project

We’re excited that support for getting and managing TLS certificates via the ACME protocol is coming to the Apache HTTP Server Project (httpd). ACME is the protocol used by Let’s Encrypt, and hopefully other Certificate Authorities in the future. We anticipate this feature will significantly aid the adoption of HTTPS for new and existing websites.

Postgres Hidden Gems

Postgres has a rich set of features, even when working everyday with it you may not discover all it has to offer. In hopes of learning some new features that I didn’t know about myself as well as seeing what small gems people found joy in I tweeted out to see what people came back from. The response was impressive, and rather than have it lost into ether of twitter I’m capturing some of the responses here along with some resources many of the features.

Why upgrade PostgreSQL?

Usage should be simple – pick from which version you want to upgrade, to which version you want to upgrade, and press gives me… button.

Introducing DNS Resolver, 1.1.1.1 (not a joke)

Cloudflare’s mission is to help build a better Internet and today we are releasing our DNS resolver, 1.1.1.1 - a recursive DNS service. With this offering, we’re fixing the foundation of the Internet by building a faster, more secure and privacy-centric public DNS resolver. The DNS resolver, 1.1.1.1, is available publicly for everyone to use - it is the first consumer-focused service Cloudflare has ever released.

My Favorite PostgreSQL Queries and Why They Matter

Below, I present a combination of 8 differing queries or types of queries I have found interesting and engaging to explore, study, learn, or otherwise manipulate data sets.

Categorias
Tropeçando

Tropeçando 80

MVCC and VACUUM

Experienced PostgreSQL users and developers rattle off the terms “MVCC” and “VACUUM” as if everyone should know what they are and how they work, but in fact many people don’t. This blog post is my attempt to explain what MVCC is and why PostgreSQL uses it, what VACUUM is and how it works, and why we need VACUUM to implement MVCC. In addition, I’ll include a few useful links for further reading for those who may want to know more.

Setting up psql, the PostgreSQL CLI

PostgreSQL ships with an interactive console with the command line tool named psql. It can be used both for scripting and interactive usage and is moreover quite a powerful tool. Interactive features includes autocompletion, readline support (history searches, modern keyboard movements, etc), input and output redirection, formatted output, and more.

A Replication Cheat-Sheet

New flexbox guides on MDN

In preparation for CSS Grid shipping in browsers in March 2017, I worked on a number of guides and reference materials for the CSS Grid specification, which were published on MDN. With that material updated, we thought it would be nice to complete the documentation with similar guides for Flexbox, and so I updated the existing material to reflect the core use cases of Flexbox.

introduction to pgBackRest

pgBackRest (http://pgbackrest.org/) aims to be a simple, reliable backup and restore system that can seamlessly scale up to the largest databases and workloads.

Instead of relying on traditional backup tools like tar and rsync, pgBackRest implements all backup features internally and uses a custom protocol for communicating with remote systems. Removing reliance on tar and rsync allows for better solutions to database-specific backup challenges. The custom remote protocol allows for more flexibility and limits the types of connections that are required to perform a backup which increases security.