What I learned from my first Black Friday in the Cloud

We survived, but not without some wounds

What a day… Black Friday in Brazil is not like in North America or Europe. We don’t have a holiday tied to it or any reason like sell old stock to renew for Christmas. It’s just a commercial date to follow global trends, and it works. Since 2012 the average rate of customer attendance in the retail company where I work raises 63% in the Black Friday’s week.

More clients lead to more work, more machines, storage and so on. Since 2012 we had a lot of changes: ERP, equipment and too many people changes. Our last big change was to move almost all of our environment to the cloud, which happened in May 2017. It’s a Hybrid Cloud in a Tier 3 Datacenter not so far, as here in Brazil the internet infrastructure latency values almost forbid you to have critical systems spread on the Cloud.

Continue reading

Advertisements

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 ftp.example.com
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 ftp.example.com -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/oracle_fdw.so": libclntsh.so.12.1: 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.

 

WHY

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.