4

I tried postgres unlogged table for using them as a cache to speed up queries and it works (50% query time got avoided). I can really recommend that to everyone.

But I restarted the server (I mean the hardware server using reboot) and now the unlogged tables are gone completely - not only the data. So I could add them after every startup manually - but that should be possible somehow automatically.

Or does anyone have a clue how to make the table structure permanently including unlogged tables as well as normal tables?

versions: postgres 18.0, debian 13.2 on a physical server.

To reboot I only did type 'reboot' in the terminal.

systemctl stop/start postgresql doesn't delete the unlogged tables, only 'reboot'

Postgres is not running in anything like docker container, but only got updated from version 9 doing a new install.

Improve this question
5
  • 1
    Add the method you are using to restart the server as text update to the question text. An unlogged table should persist across a clean restart. Commented Oct 31 at 14:55
  • I mean hardware restart. 'reboot' Commented Oct 31 at 16:54
  • 1
    So add to the question your OS, the 'hardware'(virtual/physical) and the 'reboot' process. Again for a normal clean reboot Postgres will shutdown cleanly and the tables will persist. Something in your process is not behaving properly and that can and will probably cause issues elsewhere. Commented Oct 31 at 17:04
  • 1
    I tested an unlogged table on my local instance. If I restart PostgreSQL processes cleanly with pg_ctl, the unlogged table retains its data. I tried simulating a crash by kill -9 the PostgreSQL processes, and the unlogged table is still intact (data and metadata). Maybe I am not killing the right process. Anyway, I'm not testing any further. Commented Oct 31 at 20:00
  • Is that db cluster running at OS level, directly, or is it inside docker or otherwise contained/isolated? How's storage attached? Can you give more details about the server setup, hosting, cloud provider? Commented Oct 31 at 20:38

1 Answer 1

Reset to default
1

That table should persist through any kind of restart that any other non-temp entity would. Only the contents are ever at risk.

Unlogged only affects the rows in the table, resulting from DML on it. The DDL that actually spawned the table itself, hits the system catalogs, which are by all means backed by WAL. So to somehow lose the whole table you'd have to lose a bunch of entries scattered in those catalogs. It's extremely unlikely for a crash to randomly lose, with surgical precision, the exact set of entries that create table did, or a drop would otherwise have to undo.

The distinction between whether its the table itself or its contents that are (potentially) non-persistent, is somewhat underlined in the doc, through very specific wording (bold+italic mine):

Data written to unlogged tables is not written to the write-ahead log

an unlogged table is automatically truncated after a crash or unclean shutdown.

The contents of an unlogged table are also not replicated to standby servers.

It's also not like crashes take a shotgun to your disk; you typically just lose whatever wasn't finished and flushed. File system corruption is a whole separate, albeit sometimes related thing - that's what could damage some of your data beyond recovery, but again, it's unlikely to be this clean and limited to just the unlogged tables.

The only way I see you could've lost the entire table is if you created and started using it within one long transaction you never committed, or if the whole file system went back to a state from before the whole thing (some common docker misconfigs can do that). Or, something simply dropped it by accident, directly or through a cascade.

For the sort of application you described, you might also be interested in Oracle-style Global Temporary Tables emulated with Postgres unlogged tables in pgtt extension. Main difference is that all sessions see the table, but each session only sees and is able to interact with its own rows in it, and it gets intentionally wiped similar to how regular temps are.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Zegarek made it clear: I combined all CREATE TABLES in one command without begin BEGIN; and COMMIT; It worked, but restart deleted the tables.
@Theores. 1) In what client did you run the command and add the command as update to question. 2) Also that does not explain systemctl stop/start postgresql doesn't delete the unlogged tables, only 'reboot'. FYI, since you are on Debian you should use something like this pg_ctlcluster 18/main stop.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.