Tag: database

PostgreSQL Wraparound

We had a Postgres server go into read-only mode — which provided a stressful opportunity to learn more nuances of Postgres internals. It appears this “read only mode” something Postgres does to save it from itself. Transaction IDs are assigned to each row in the database — the ID values are used to determine what transactions can see. For each transaction, Postgres increments the last transaction ID and assigns the incremented value to the current transaction. When a row is written, the transaction ID is stored in the row and used to determine whether a row is visible to a transaction.

Inserting a row will assign the last transaction ID to the xmin column. A transaction can see all rows where xmin is less than its transaction ID. Updating a row actually creates a new row — the old row then has an xmax value and the new row has the same number as its xmin — transactions with IDs newer than the xmax value will not see the row. Similarly, deleting a row updates the row’s xmax value — older transactions will still be able to see the row, but newer ones will not.

You can even view the xmax and xmin values by specifically asking for them in a select statement: select *, xmin, xmax from TableName;

The transaction ID is stored in a 32-bit number — making the possible values 0 through 4,294,967,295. Which can become a problem for a heavily I/O or long-running database (i.e. even if I only get a couple of records an hour, that adds up over years of service) because … what happens when we get to 4,294,967,295 and need to write another record? To combat this,  Postgres does something that reminds me of the “doomsday” Mayan calendar — this number range isn’t aligned on a straight line where one eventually runs into a wall. The numbers are arranged in a circle, so there’s always a new cycle and numbers are issued all over again. In the Postgres source, the wrap limit is “where the world ends”! But, like the Mayan calendar … this isn’t actually the end as much as it’s a new beginning.

How do you know if transaction 5 is ‘old’ or ‘new’ if the number can be reissued? The database considers half of the IDs in the real past and half for future use. When transaction ID four billion is issued, ID number 5 is considered part of the “future”; but when the current transaction ID is one billion, ID number 5 is considered part of the “past”. Which could be problematic if one of the first records in the database has never been updated but is still perfectly legitimate. Reserving in-use transaction IDs would make the re-issuing of transaction IDs more resource intensive (not just assign ++xid to this transaction, but xid++;is xid assigned {if so, xid++ and check again until the answer is no}; assign xid to this transaction). Instead of implementing more complex logic, rows can be “frozen” — this is a special flag that basically says “I am a row from the past and ignore my transaction ID number”. In versions 9.4 and later, both committed and aborted hint bits are set to freeze a row — in earlier versions, used a special FrozenTransactionId index.

There is a minimum age for freezing a row — it generally doesn’t make sense to mark a row that’s existed for eight seconds as frozen. This is configured in the database as the vacuum_freeze_min_age. But it’s also not good to let rows sit around without being frozen for too long — the database could wrap around to the point where the transaction ID is reissued and the row would be lost (well, it’s still there but no one can see it). Since vacuuming doesn’t look through every page of the database on every cycle, there is a vacuum_freeze_table_age which defines the age of a transaction where vacuum will look through an entire table to freeze rows instead of relying on the visibility map. This combination, hopefully, balances the I/O of freezing rows with full scans that effectively freeze rows.

What I believe led to our outage — most of our data is time-series data. It is written, never modified, and eventually deleted. Auto-vacuum will skip tables that don’t need vacuuming. In our case, that’s most of the tables. The autovacuum_freeze_max_age parameter sets an ‘age’ at which vacuuming is forced. If these special vacuum processes don’t complete fully … you eventually get into a state where the server stops accepting writes in order to avoid potential data loss.

So monitoring for transaction IDs approaching the wraparound and emergency vacuum values is important. I set up a task that alerts us when we approach wraparound (fortunately, we’ve not gotten there again) as well as when we approach the emergency auto-vacuum threshold — a state which we reach a few times a week.

Using the following query, we monitor how close each of our databases is to both the auto-vacuum threshold and the ‘end of the world’ wrap-around point.

WITH max_age AS ( SELECT 2000000000 as max_old_xid
                        , setting AS autovacuum_freeze_max_age FROM pg_catalog.pg_settings 
                        WHERE name = 'autovacuum_freeze_max_age' )
         , per_database_stats AS ( SELECT datname , m.max_old_xid::int 
                        , m.autovacuum_freeze_max_age::int 
                        , age(d.datfrozenxid) AS oldest_current_xid 
                        FROM pg_catalog.pg_database d 
                        JOIN max_age m ON (true) WHERE d.datallowconn ) 

SELECT max(oldest_current_xid) AS oldest_current_xid 
      , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound 
      , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac FROM per_database_stats

If we are approaching either point, e-mail alerts are sent.

When a database approaches the emergency auto-vacuum threshold, we freeze data manually —  vacuumdb --all --freeze --jobs=1 --echo --verbose --analyze (or –jobs=3 if I want the process to hurry up and get done).

Accessing Postgresql Server Through SSH Tunnel

Development servers can be accessed directly, but access to our production Postgresql servers is restricted. To use a SQL client from the VPN network, you need to connect through an SSH tunnel.

In the connection configuration for a database connect, click the “SSH” tab.

You can one-off configure an SSH tunnel for a connection, but the most efficient approach for setting up the SSH tunnel used to connect to all of our production databases is to create a tunnel profile. The profile lets you type connection info in one time and use it for multiple servers; and, when you need to update a setting, you only have one configuration set to update.

To create a profile, click the pencil to the right of the profile drop-down box.

Under the “Network Profiles” section, click “Create” to create a new profile.

Give the profile a descriptive name and click “OK”.

Check the “Use SSH Tunnel” box, enter the hostname (one of the ETL dev boxes is a good choice in our case – ltrkarkvm553.mgmt.windstream.net. Supply the username for the connection. You can use password authentication or, if you have a key exchange set up for authentication, select public key authentication. Click “Apply and Close” to save the profile.

Your profile will appear in the profile drop-down – select the profile …

And all of the settings will pop in – they’re grayed out here, if you need to update the SSH tunnel profile settings, click the little pencil again.


Adding MariaDB/MySQL Users

Quick notes on creating a database user — MariaDB and MySQL use a combination of username and source host to determine access. This means ‘me’@’localhost’ and ‘me’@’remotehost’ can have different passwords and privilege sets. How do you know what the hostname is for your connection? I usually try to connect and read the host from the error message — it’ll say ‘someone’@’something’ cannot access the database.

# Create a user that is allowed to connect from a specific host
create user 'username'@'hostname' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'hostname';

# Create a user that is allowed to connect from a specific IP
create user 'username1'@'' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username1'@'';

# Create a user that is allowed to connect from database server
create user 'username2'@'localhost' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username2'@'localhost';

# Create a user that is allowed to connect from any host
create user 'username3'@'%' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username3'@'%';

# Flush so new privileges are effective
flush privileges;

# View list of database users
SELECT User, Host FROM mysql.user;
| User           | Host       |
| username3      | %          |
| username2      |   |
| username       | hostname   |
| root           |  |
| root           | ::1        |
| root           | localhost  |
6 rows in set (0.000 sec)

Oracle Password Expiry – Sandbox Server

Oracle 11g seems to ship with password expiry enabled — which is a very good thing for production systems. I’ve even written some code to maintain our system account password (scripts are grabbing the password from a not-clear-text storage facility anyway, so it wasn’t a big deal to add an n-1 password and move the current stashed password into the n-1 column, change the account password, and stash the updated password in the current password location … now my system ID password is updated by a monthly cron job, no one actually knows the password {although anyone could find it, so I would run the password cycle script when individuals leave the group}). But I’m a lot lazier about this stuff in my sandbox. Proof of concept code has clear text passwords. But the server is bound to localhost & there’s no real data in, well, anything.

I started seeing lines in my error log indicating the password would expire. Aaaand that’s how I learned that password expiry was enabled by default now.

[Sat Apr 18 07:42:59 2020] [error] [client] PHP Warning: oci_connect(): OCI_SUCCESS_WITH_INFO: ORA-28002: the password will expire within 7 days in /var/www/vhtml/…/file.php on line 191, referer: …

I’m going to disable password expiry because it’s a sandbox. For a real system, obviously, this may not be a stellar idea.

select USERNAME, ACCOUNT_STATUS, PROFILE from dba_users where USERNAME = 'SampleUser';




Note the account status “EXPIRED(GRACE)” — that’s why I am getting the error shown above. Grab the profile name — it’s a sandbox, so 99% sure it’s going to be ‘DEFAULT’ and alter that profile with an unlimited password expiration:

alter profile <profile_name> limit password_life_time UNLIMITED;

Except that didn’t actually stop the error. Turns out you’ve still got to change the password once the account has been flagged as expired (or let the password expire and then unlock the account … but I was looking at the log because I’m debugging something, and I wanted the error to stop *right now*).

alter user SampleUser identified by N3W_P@s5_w0rD;