Careful using native PostgreSQL function with PgBouncer

Just to give my two cents exposing a problem that I had a week ago when one of ours databases repeatedly entered in recovery mode because of a query.

Let’s put some emotion and start from the beginning.

Suddenly everyone starts to complain that the ERP is down, the company is losing money, the end of the world is near and some heads gonna roll.

With 15 people around you, you calmly check space left, load average, database connections an so on.

After some researching at the log, you could spot a query looking for an invalid memory address. After some debugging, that’s what I’ve found:

To avoid rewriting on a function, it was used a native function from PostgreSQL to get the IP and port from the database so the move from the test server to the production server would be easier. The functions are inet_server_addr() and inet_server_port().

It’s not a problem when your test and/or dev environments are equal to the production. As we don’t have to treat too many connections in the test environment, we do not use PgBouncer, a very light connection pooler.

Because of a very specific demand, we need to connect out of a transaction to get some values inside a running function, so we use a method from a C++ library to connect to the database, get a value and update it, returning to the original function an integer.

When that function was called in the production environment, PGBouncer returns nothing or some trash, and the C++ function, for some reason, tries to reach an invalid memory address and the database, to preserve itself, enters into recovery mode.

To fix the whole problem, we have a lot of solutions:

  • Rewrite the C++ function to validate the parameters, which will not resolve the PgBouncer thing;
  • Put fixed values for IP and port at the functions;
  • Create a user for this specific call and connect to the main port, instead of the PgBouncer port, which is by far the worst solution;
  • Diminishing this type of problems by creating dev/test environments equal the production.

Not a difficult problem to fix, neither to spot, but indeed a fatal error.


How to send files using Windows FTP using task scheduler

Hello, everyone.

In this post, I will try to save all the steps that I’ve used to transfer some files to a third party company from one of my clients.

It is a not so usual type of file transferring action as I used to do because 99% of my clients use Linux instead of Windows on their servers.

It’s quite easy to reproduce for much needs so this article will be very short.

Firstly you will need to create a .bat file. I prefer to create all at the same place and navigate into the directories inside the file, but you can place it where the file is, of course.

Edit the .bat file and write the commands as below, if you will use the native ftp client from Windows:

cd c:\folder
@echo off <-- Not use this line while testing 
echo user ftpuser> ftpcmd.dat
echo password>> ftpcmd.dat
echo bin>> ftpcmd.dat
echo put file1.ext>> ftpcmd.dat
echo put file2.ext>> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -d -n -s:ftpcmd.dat
del ftpcmd.dat

You can use the FTPS solution from MOVEit Freely , it’s pretty much the same:

cd c:\folder
echo off 
echo bin> ftpcmd.dat
echo put file1.ext>> ftpcmd.dat
echo put file2.ext>> ftpcmd.dat
echo quit>> ftpcmd.dat
ftps -a -user:ftpuser -password:password -s:ftpcmd.dat 
del ftpcmd.dat

That’s being done, let’s put it to send every day by the Windows Task Scheduler, by creating a basic task.

Until the next!

Importing from Oracle to PostgreSQL using CSV file OR How to get Oracle data without a Foreign Data Wrapper

Hi, folks.

At my work, my boss needed urgently a report involving PostgreSQL and Oracle Databases. As almost the situations that already uses data from both are gathered by PHP, I’ve never had the need to install oracle_fdw. So I started to install it and to make the SQL. At some point, I encountered a problem with a library.

ERROR: could not load library "/usr/local/pg936/lib/postgresql/": cannot open shared object file: No such file or directory

The problem, in fact, is not a problem. You just need to restart the database.

– Wait a minute! You just said the word “restart”??? (Ernst-Georg Schmid commented that not in all cases we need to restart, so please check the comment out to see if it fits in your case)

Well, I had that problem too. There was no possibility to restart anything at the very moment and my boss was waiting for the report. So I needed to do it by another way.

Doing two queries and copy into an electronic sheet could be a possibility, but not very effective when you need to do a lot of references between them, so I tried another solution.

All this text was to give some basis to the following, don’t worry, I will be more technical from now on.

Continue reading

Audit Log and Partitioning

Hi all,

For those who have to log high load databases, and worse, need to keep this logs for a long time and always available, partitioning is the way.

Of course, you can create various log tables, splitting by hand, but it is not efficient.

To solve this demand, I’ve used two very well explained tutorials with some modifications. The first is the Audit Trigger from the official Wiki. The second is the Scaling PostgreSQL Performance Using Table Partitioning from Engine Yard Blog.

In this tutorial, I will create a log that records every data change on the tables where the audit trigger is enabled and it will be partitioned by month and year. Feel free to use the original tutorials, as they cover more situations, especially the Engine Yard’s one, that has a solution to store the old data in files after some time.

Continue reading

Changing a Directory PostgreSql Database

Well, it seems simple just reading the title, and it should be simple, anyway. Unless you have work with tablespaces.



There are a lot of reasons to use tablespaces: to organize, to split data into two or more partitions and/or to separate data into different disks because of speed, for example.

Whatever the reason is, the cluster will create symbolic links on the file system, they are stored at data/pg_tblspc/ and point to the real path that you set when the tablespace was created. As the documentation advises, avoid to mess with this. Try to move data inside Postgres before, unless you need to change the entire cluster, which is what I will cover in this article.

Besides that my case was very specific, some steps should be common for any database working with tablespaces.

Continue reading

Accidental DBA (another one)

Yeah, I know, everybody is tired of accidental DBA stories. In fact, me too.

As another one of the kind, for each new challenge in front of me, I did start to research about DBA’s gold rules, principles and so one. Every time one of the results led me to an article titled Accidental DBA.

Almost all of them about SQL Server, a little about Oracle and PostgreSQL. All of them were interesting because of a destiny’s joke, I became a MySql, SQL Server, PostgreSQL and Oracle DBA… (in a not so distant future, Sybase or SAP HANA)

Those stories inspired me, but they were just it, stories. There was a lack of “how to do” stuff, besides the few ones about PostgreSQL that I could found. One of the best is this tutorial by Josh Berkus.

There are those who say that every DBA became a DBA by accident. It was not my case, indeed. After some years developing, I had that will to interact with data, servers, machines and stuff like that. I’ve pursued to become a DBA, even not knowing what it really meant.

The reasons that made me ask to be a DBA:

  1. Need to learn something new;
  2. After an ERP change, almost my tasks was related to fixing data errors. Errors made by bad programming and/or modeling;
  3. The ERP’s software house were too slow to attend our requests;
  4. Lack of maintenance of the database.

We never had a DBA, so I become one with all the responsibilities and almost no baggage. It was crazy and still is.

As a DBA, I’m more inclined to infrastructure than development, so I do a lot of system administration too. It seems common to this role, as most of the DBAs that I follow know a lot of it, except in some corporations that have some deep definitions about what every role has access.

The Never Ending Search For a Better Life or Why I Need to Develop Again

Hi folks,

I’ve created this blog for a purpose. To share knowledge, to document details of some tasks to remember faster about little caveats and to be seen. However, write it down in a way that everyone could understand, and sometimes, the need to argue with an “I know everything after find on google” genius takes some time, so I prefer to write a really good post once in a while instead to write a lot of mediocre posts every day.

All of it started some time ago when I’ve realized that I was not fitting in here anymore (Brazil). The turning point was when, again, more than 50% of voting people had chosen our actual president.

When you dislike something so hard, an event can break some chains and that was what happened to me. To everywhere I’d look, I felt bad, and a lot of minor people’s acts started to piss me off very quickly.

Some days after, I saw a news about people choosing Canada to learn English by its low costs and etc., so, as my English was too rusty (in fact, I’d never attend a class for more than two months), it gave me the idea to learn to talk English once and for all, because, without it, it was useless to do any planning about leaving the country.

As going to Canada to increase my English would be out of my budget, I started to save some time to study online. However, I did some research about how people come back after study in Canada, and to my surprise, almost all of them said: “I want to go back [to Canada] and stay forever”.

Well, one thing brings you to another, so I started to research about Brazilians in Canada. I’ve found a lot, and no one complaining, really… Being said that, to live in Canada became my new dream, and believe, a lot of levels higher than the last one.

I started to study about immigration processes and the IT market. About two months of Brazilian’s YouTube channels, showing all the marvels of living in a fair country, led me to start my family’s processes to leave Brazil.

Well, as many of you may know, it is not a simple task. Indeed, it is very hard, but not impossible. There are more than 50 ways to immigrate, but it will directly depend on your profile. IT market is hot in Canada, but they seek for very specific specialists, so a job offer overseas is almost impossible for me, as a jack of all trades. The government process is quite difficult too. You have to reach some points to be eligible to go there and look for work, but most of the points are given for who already worked and/or studied there unless you are a Ph.D. with band 8+ IELTS.

There are provincial processes too, they are less tough, however, you must be there already. What remains? Colleges and Universities. Yes, you can go there to study in a public institution and get the right to work there for a time. It will increase a lot your chances to be eligible for one or more of the immigration processes. In fact, it is the easiest way, if you have a lot of money, because of international students, on average, pay CAD 10k more for the year than the natives. So, be prepared to spend from CAD 20k to 30k for a year, the cost of living included (it is an estimate provided by their government website).

I’ve decided to specialize in something related to Software Development. There are so many reasons that I could forget to list all of them, but I’ll give a try:

  • You will never be an ex-Developer. As a DBA I need to code and to review codes a lot;
  • I feel slightly happier when I finish a code than when I finish a Database troubleshooting (may I say that it is more like a relief);
  • It is quite easier and faster to learn new technologies and almost all of the jobs don’t need so much experience . For SysAdmin and DBA roles, 8+ years of experience and a lot of certifications are common requirements;
  • This article: Tech Jobs Will Boom In Canada, But Country Lacks People To Fill Them;
  • Work as a freelancer. The best way to gain money independence nowadays. (I’m not against work as an employee, of course. In fact, you can do both (as I always did)).

See you there…