2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • Support & Services
    • Support
      • 24/7 PostgreSQL Support
      • Developer Support
    • DBA Services
      • Remote DBA
      • Database Monitoring
    • Consulting Services
      • Health Check
      • Performance Tuning
      • Database Security Audit
      • PostgreSQL Upgrade
    • Migration Services
      • Migrate to PostgreSQL
      • Migration Assessment
  • Products
    • Postgres-BDR ®
    • PostgreSQL High Availability
    • Kubernetes Operators for BDR & PostgreSQL
    • Managed PostgreSQL in the Cloud
    • Installers
      • Postgres Installer
      • 2UDA
    • 2ndQPostgres
    • pglogical
    • Barman
    • repmgr
    • OmniDB
    • SQL Firewall
    • Postgres-XL
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • AlwaysOn Postgres
      • PostgreSQL with High Availability
      • Security Best Practices
      • BDR
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • AlwaysOn Postgres
      • PostgreSQL with High Availability
      • Security Best Practices
      • BDR
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • What Does “2ndQuadrant” Mean?
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Craig's PlanetPostgreSQL3 / Putting a PostgreSQL tablespace on a ramdisk risks ALL your data
craig.ringer

Putting a PostgreSQL tablespace on a ramdisk risks ALL your data

June 16, 2014/15 Comments/in Craig's PlanetPostgreSQL /by craig.ringer

I periodically see people being advised to put their tablspaces on RAM disks or tempfs volumes. This is very bad advice. Do not put a PostgreSQL TABLESPACE on a RAM disk or tempfs.

Why you shouldn’t put a tablespace on a ramdisk

Unlike MySQL and some other databases, PostgreSQL tablespaces are not completely independent of the rest of the database system. You can’t just throw a tablespace away and have the rest of the database system keep on working.

Most importantly, the write-ahead log (WAL) that provides PostgreSQL’s crash safety is stored in pg_xlog, and is shared across all tablespaces. PostgreSQL expects to be able to replay this log in order and without errors after a crash or shutdown. Until the log has replayed, the database is assumed to be in an unsafe state and connections will be refused with:

FATAL:  the database system is starting up

If the write-ahead log contains changes to tables/indexes/etc in a tablespace that no longer exists, PostgreSQL cannot replay the WAL. It will get stuck, and will never start up successfully, or will abort startup. You will be unable to access any of your tables on any database in that PostgreSQL instance. PostgreSQL will fail to start with an error like:

LOG:  could not open tablespace directory "pg_tblspc/16389/PG_9.4_201405111": No such file or directory
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/89ECF00
FATAL:  could not create directory "pg_tblspc/16389/PG_9.4_201405111": No such file or directory
CONTEXT:  xlog redo insert: rel 16389/13060/16390; tid 8283/206
LOG:  startup process (PID 28209) exited with exit code 1
LOG:  aborting startup due to startup process failure

PostgreSQL can’t just throw the changes in the transaction logs away, because it doesn’t know you meant to remove the tablespace. What if that’s your tablespace for vitally important accounting data on a separate encrypted hard drive, and the admin hasn’t entered the password required to mount the drive yet? Or, more likely, what if you moved it and forgot to update the tablespace symlink? You don’t want PostgreSQL saying “oh, I guess you didn’t want that data after all”.

So. Don’t put a tablespace on a ramdisk.

The same problem affects removable storage. It’s fine to put a tablespace on removable storage, but you can’t remove the storage until you first drop the tablespace.

How this could improve

There are a few improvements we could make to PostgreSQL in this area. I’ve already helped make one of them: 9.4 now has a warning about this admin error in the documentation.

One useful, albeit platform specific option, would be to try to detect temporary storage and warn the user. It would be ultimately pretty futile, though – what about removable storage? What if the user creates the tablespace on durable storage then moves the symlink? What about a SAN volume on non-durable storage? etc. It’d be at best an attempt to catch obvious admin mistakes. It might still be useful, but it’s not clear that it’s worth adding the complexity required to implement it.

Instead, we need two things:

  • An option that can be used to recover from this mistake. Akin to zero_damaged_pages, the (currently nonexistent) discard_missing_tablespaces option would throw away all WAL writes destined to tablespaces that do not currently exist. It would have to mark the tablespace as broken in the system catalogs so all attempts to access it failed. The admin could restart PostgreSQL with this option enabled to recover from an error like this, or could even run with it always enabled if they regularly used tablespaces on temporary storage
  • A TEMPORARY TABLESPACE feature that marks a tablespace as disposable. If PostgreSQL sees WAL writes to a tablespace marked TEMPORARY and it doesn’t exist, it would discard the writes and drop the tablespace.

Recovery from lost tablespaces

I’ll write about options for recovering databases where you’ve discarded/lost an in-use tablespace in a follow-up post, along with some suggestions on what you can do safely as an alternative. In the mean time, here’s a Stack Overflow post I wrote a while ago about how to improve PostgreSQL’s performance for unit/integration testing without relying on ramdisks.

If you’re seeing errors like:

ERROR:  could not open file "pg_tblspc/16384/PG_9.4_201405111/13060/16385": No such file or directory

from a psql session, you might just be able to DROP TABLESPACE and DROP TABLE. Recovery is only a serious problem when the database cannot start because of WAL records that cannot be replayed.

At least on PostgreSQL 9.4, you can also just create an empty directory where PostgreSQL expects to find the tablespace. It will assume that you’ve lost the tablespace contents and will proceed with recovery, discarding changes that apply to that tablespace.

Tags: corruption, PostgreSQL, sysadmin, tablespace
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
15 replies
  1. Misha
    Misha says:
    June 16, 2014 at 2:47 pm

    It is save from crash in case you DO have standby. just switch to new one

    Reply
  2. alhashash
    alhashash says:
    June 16, 2014 at 6:01 pm

    It should work if the tablespace has only unlogged tables which should not create any xlog entries except for DDL queries.

    Reply
    • craig.ringer
      craig.ringer says:
      June 17, 2014 at 2:00 am

      Exactly. It’s that “except” that’s a concern.

      You can get away with a tablespace on volatile storage much of the time, but it’s risky, and it’s usually unnecesary given how well Linux’s virtual memory management works. UNLOGGED tables + a tablespace on a separate filesystem are generally quite sufficient.

      Reply
  3. Misha
    Misha says:
    June 17, 2014 at 9:05 am

    It is save.

    |MASTER + TMPFS| === wal ===> |STANDBY + TMPFS|

    If there is any crash happens on MASTER — then just switch to standby.
    It is simple like any others cases. TMPFS or NOT TMPFS doesn’t matter at all.

    Reply
    • craig.ringer
      craig.ringer says:
      June 17, 2014 at 9:12 am

      Sure, that’s the same logic as using an EC2 instance store. The database instance might be destroyed on crash, but you just fail over to your synchronous replica.

      That doesn’t make the use of a tablespace on a tempfs/ramdisk or other volatile storage any less likely to destroy that particular PostgreSQL instance.

      After all, you can rm -rf /var/lib/pgsql/9.3/data and then recover from a standby, too. That doesn’t make it safe for the instance that just got deleted.

      Reply
  4. Diego
    Diego says:
    June 21, 2014 at 2:13 am

    What about putting the “temp_tablespaces” on a RAM disk? Is it safe since in “temp_tablespaces” only TEMP TABLES are stored?

    Reply
  5. humberto.ibanez
    humberto.ibanez says:
    July 31, 2015 at 2:49 pm

    We done some tests with tablespaces at Ramdisk, in which we create only unlogged tables. Using the Postgresql-9.4 we done:

    1) remove data from Ramdisk with rm command or rename the symlink,
    2) restart the postgresql.
    The postgresql was restarted without problem.

    Would be secure maintain the tablespaces on Ramdisk that are populated exclusively by unlogged tables?

    Reply
    • humberto.ibanez
      humberto.ibanez says:
      July 31, 2015 at 2:55 pm

      The log_statement is configured as ddl.

      Reply
    • craig.ringer
      craig.ringer says:
      August 1, 2015 at 1:03 am

      Yes, that’s fine so long as you don’t mind having to re-create the tablespace directory. You might have to drop & recreate the tables after restart too.

      Reply
  6. Frank
    Frank says:
    August 7, 2015 at 12:42 pm

    I have requirements for a table that is volatile. The usage would basically be that of a scratchpad. When the server is online it will write and use this table. If the data or table is lost, there is no negative as the server will just recreate this scratchpad. I had thought that using a RAM disk or storing the table in memory would be wise since I would gain additional speed and performance. There will be many reads/writes to this table and since data integrity is not of concern, the performance is all that needs focus.

    Reading your article, I start to see this is a bad idea. It seems PostgreSQL wouldn’t handle this well. You linked to a post on stackoverflow that goes into a bit more detail of explaining things you can do to increase performance. Specifically you mentioned using ‘UNLOGGED’ and also creating a new cluster using initdb on a RAM disk.

    Is there more I can read regarding the benefits and drawbacks of both ‘UNLOGGED’ and creating a new cluster? How they compare and the advantages each have over the other?

    Reply
  7. qris
    qris says:
    February 22, 2016 at 11:57 am

    It seems that temporary tables are a subtype of unlogged tables (http://rhaas.blogspot.co.uk/2010/05/global-temporary-and-unlogged-tables.html). Therefore there should not be any WAL entries for temporary tables, and it’s safe to keep them on a ramdisk using temp_tablespaces. So the above warning should not apply to tablespaces that are only used for temporary tables?

    Reply
    • craig.ringer
      craig.ringer says:
      February 22, 2016 at 12:19 pm

      Yep, it should be safe(ish) for tablespaces containing only TEMPORARY or UNLOGGED tables. PostgreSQL might refuse to start up thoug until you re-create the tablespace directory after restart. It probably also won’t recreate the relfilenodes for the unlogged tables properly so you may not be able to actually use the UNLOGGED tables, but it shouldn’t stop the rest of Pg working.

      Reply
  8. ergo
    ergo says:
    May 6, 2016 at 6:24 am

    While I can confirm that UNLOGGED tables work with tablespaces in RAM, I also found, that a RAMdisk is no magic bullet.

    What you can expect:

    a) faster writes, but the increase is not dramatic if your server is correctly configured for fast writes anyway and you are already using UNLOGGED tables

    b) consistent read performance from the first query onwards

    What you can’t expect:

    c) much faster queries if you have enough RAM for the OS filesystem cache and shared_buffers anyway. Once caching kicks in, the read performance will equal that of a RAMdisk

    So, if you don’t need b), your money is better spent on server class SSDs and the RAM is better dedicated to caching. And if you need b), I’d recommend trying pg_prewarm and maybe pg_hibernator first.

    Reply
  9. Artur
    Artur says:
    March 24, 2018 at 6:16 pm

    What do you think about the following approach?
    – Main PostgreSQL as source of truth
    – Other PostgreSQL in RAM (https://www.manniwood.com/postgresql_94_in_ram/index.html), connected with postgres_fdw.

    Reply
    • craig.ringer
      craig.ringer says:
      March 26, 2018 at 4:58 am

      What would that achieve? I don’t see the point.

      As you will have seen in the article, you can achieve similar performance benefits with unlogged tables, dirty-writeback tuning, etc anyway.

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Announcing Barman 1.3.1 PostgreSQL’s CTEs are optimisation fences
Scroll to top
×