I shouldn't be the first to need to export data from a PostgreSQL database installed on Windows with a win1252 code to a database with UTF-8 code (in my case, on a Linux server).
It is not enough to transform the transfer file to UTF-8, as the characters of win1252 (left double quote, right double quote, single quote and dash) will be there, with a weird value in your database.
In my experience, I had to import data as-is and afterwards perform an update using the function to rewrite data for UTF-8 characters correctly.
The following code examples are for: 1 - transform to HTML characters; 2 - transform to single characters.
-- DROP FUNCTION substitui_win1252_html(texto); CREATE OR REPLACE FUNCTION substitui_win1252_html(texto text) RETURNS text AS $$ BEGIN texto := replace(replace(replace(replace(replace(texto, '', '’'), '', '“'), '', '”'), '','•'), '', '–')::text; RETURN texto; END; $$ LANGUAGE plpgsql;
-- DROP FUNCTION substitui_win1252(texto); CREATE OR REPLACE FUNCTION substitui_win1252(texto text) RETURNS text AS $$ BEGIN texto := replace(replace(replace(replace(replace(texto, '', ''''), '', '"'), '', '"'), '','•'), '', '-')::text; RETURN texto; END; $$ LANGUAGE plpgsql;
Don't worry about the squares that you might see. If you copy it to a good text editor, you will their actual value.
O uso de comandos SQL, na maioria das linguagens de programação e gerenciadores de bancos de dados que suportam esta linguagem de manipulação de dados, pode ser parametrizado com variáveis de ligação (bind variables). Este recurso que, para um programador desavisado e inexperiente, pode parecer uma burocracia desnecessária, na verdade é um mecanismo muito importante para trazer segurança e eficiência ao uso de SQL em programas. Veja porque e como.
Documentation for preventing SQL injection in PHP projects. Many web developers are unaware of how SQL queries can be handled and assume that an SQL query is a reliable command. It means that SQL queries are able to bypass access controls undetected, therefore bypassing standard authentication and authorization checks, and sometimes SQL queries can allow command access at the server operating system level.
Segundo a chamada deste artigo da edição internacional da Linux Magazine, a história do Linux pode ser medida com base nas versões deste kernel, mas também pode ser medida pelas suas principais distribuições.
O Scrum é uma metodologia ágil para Gerenciamento de Projetos.
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.
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.
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.
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.
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.
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.
This post goes through multiple approaches to mocking, stubbing and spying on the date constructor using Jest.
A history about the importance of know your time and to not get stuck.
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 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.
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.
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?
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. 🙂