You can run PHP code inside PostgreSQL database.
Bash scripts. Almost anyone needs to write one sooner or later. Almost no one says “yeah, I love writing them”. And that’s why almost everyone is putting low attention while writing them.
I won’t try to make you a Bash expert (since I’m not a one either), but I will show you a minimal template that will make your scripts safer. You don’t need to thank me, your future self will thank you.
A comprehensive list of books on Software Architecture.
People in the software industry have long argued about a definition of architecture. Ralph Johnson famously defined software architecture as "the important stuff (whatever that is)." I, subjectively, followed his definition while deciding whether or not to include a specific book.
Great tips for using asynchronous communication in your favour. It’s the reason why some remote teams thrive while others grapple with anxiety and exhaustion.
Most people in the SQL and in the PostgreSQL community have used the LIMIT clause provided by many database engines. However, what many do not know is that LIMIT / OFFSET are off standard and are thus not portable. The proper way to handle LIMIT is basically to use SELECT … FETCH FIRST ROWS. However, there is more than meets the eye.
PostgreSQL can apply parallel processing to speed up query performance. But the PostgreSQL query planner and internals are complex, making it a challenge to predict how parallel processing will affect different queries.
In this webinar, you’ll learn parallelism best practices to maximize your PostgreSQL query performance.
A SYSTEMATIC FRAMEWORK FOR TECHNICAL DOCUMENTATION AUTHORING.
The Diátaxis framework aims to solve the problem of structure in technical documentation. It adopts a systematic approach to understanding the needs of documentation users in their cycle of interaction with a product.
The Conventional Commits specification is a lightweight convention on top of commit messages. It provides an easy set of rules for creating an explicit commit history; which makes it easier to write automated tools on top of. This convention dovetails with SemVer, by describing the features, fixes, and breaking changes made in commit messages.
Awesome list of awesome technology related things
Graphical cheat-sheet for PostgreSQL Observability views and functions. You have a visual representation of the services touched by each observability tool, such as buffer, write-ahead logs, query plan, table storage, index information, etc.
This document provides an introduction to tuning PostgreSQL and EDB Postgres Advanced Server (EPAS), versions 10 through 13. The system used is the RHEL family of linux distributions, version 8. These are only general guidelines and actual tuning details will vary by workload, but they should provide a good starting point for the majority of deployments.
In this post, we’re continuing the deep dive on API Gateway. Here, we’ll be looking at API Gateway access logging. Access logging can save your bacon when debugging a gnarly API Gateway issue, but you need to understand some nuance before you can use it correctly. We’ll dig into the details here so that you’ll be logging like Paul Bunyan in no time.
Netflix considerations related to the decisions of using Hexagonal Architecture
Modern, more secure recommendations continue to replace some of OAuth's original elements as the protocol evolves. Securing access to APIs and other resources and data effectively under OAuth 2.0 requires first learning the components and tools it involves.
Some benchmarks for use of auto_explain. It seems great to be enabled on production.
Here we’ll be looking into the overhead of auto_explain. Many places warn about its overhead, but I’ve found concrete information difficult to come by.
PostgreSQL is a secure database and we want to keep it that way. It makes sense, then, to consider SSL to encrypt the connection between client and server. This posting will help you to set up SSL authentication for PostgreSQL properly, and hopefully also to understand some background information to make your database more secure.
Transform every element on any website into Tailwind CSS
This website lists workshops created by the teams at Amazon Web Services (AWS). Workshops are hands-on events designed to teach or introduce practical skills, techniques, or concepts which you can use to solve business problems.
The Well-Architected framework has been developed to help cloud architects build the most secure, high-performing, resilient, and efficient infrastructure possible for their applications. This framework provides a consistent approach for customers and partners to evaluate architectures, and provides guidance to help implement designs that will scale with your application needs over time.
This repository contains documentation and code in the format of hands-on labs to help you learn, measure, and build using architectural best practices. The labs are categorized into levels, where 100 is introductory, 200/300 is intermediate and 400 is advanced.
false assumptions that programmers new to distributed applications invariably make.
Some use cases to understand this powerful yet misunderstood feature of DynamoDB. There are also examples of bad use of it.
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.
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 is a beautiful, open source, tunnel application that allows you to share your local websites with others via the internet.
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.
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?
Systemd allows you to configure a service so that it automatically restarts in case it’s crashed.
log_line_prefix should be the most-neglected postgres feature. Overused and mis-configured. The author talk about his finding, the great use and some tips for log_line_prefix configuration. This feature is very powerful on PostgreSQL.
A decoder plugin to enable logical replication from a PostgreSQL (as publisher) to MongoDB (as subscriber).
A curated list of Docker Compose samples.
These samples provide a starting point for how to integrate different services using a Compose file and to manage their deployment with Docker Compose.
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_restore strategy for a long time, but this time I would rather use
Let's dive into how to do it.
Table of contents:
- Check upgrade consistency
- Set locale
postgresql12-server contains everything needed to run the server, but my databases use some extensions , then I will add
postgresql12-contrib to be able to compile and to install the extensions.
yum install postgresql12-server postgresql12-devel postgresql12-contrib
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*
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 , 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 command is the same that we run before, without
~% /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
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. 🙂
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.
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.
how to traverse nested array structures with potentially non-existing keys without throwing notices
Proposal for thinking Laravel applications using DDD approach. A blog series for PHP developers working on larger-than-average Laravel projects.
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.