0

I'm fairly new to Postgres, having spent years with MySQL/MariaDB. I have a Postgres 14.9 database for development with two schemas in it. When I lose power, both of the schemas still exist in the database, but the data for one of the schemas is gone. The log says:

[1199] LOG:  database system was not properly shut down; automatic recovery in progress
[1199] LOG:  redo starts at 0/3094A290
[1199] LOG:  invalid record length at 0/3094A378: wanted 24, got 0
[1199] LOG:  redo done at 0/3094A340 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
[1050] LOG:  database system is ready to accept connections

Since this is for development, losing that data isn't a big deal; I just keep importing a dump, but I'd like to know why this is happening and what I can to to prevent it, especially when I take this system to production. I haven't changed any settings in Postgres or in the OS regarding disk cache. I'm using Ubuntu 22.04 in a VM, and the host is Windows 10 on an SSD.

Also, is there a way I can recover the data without importing my dump? I did some research which led me to try pg_resetxlog and pg_resetwal, but both said command not found.

Improve this question
1
  • Perhaps your VM is ignoring fsync to speed up IO? This is a complex durability issue that the database itself cannot fix: if any of the IO layers (guest OS, hypervisor, host OS, disk controller, SSD itself) does not perform fsync correctly, then data can be lost.
    – Melkij
    Commented Aug 21, 2023 at 16:29

1 Answer 1

Reset to default
1

I figured it out. I created my tables as UNLOGGED, and forgot to run my other file that sets them to LOGGED. That seems to be expected behavior.

From https://www.postgresql.org/docs/current/sql-createtable.html

If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log (see Chapter 30), which makes them considerably faster than ordinary tables. However, they are not crash-safe: 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. Any indexes created on an unlogged table are automatically unlogged as well.

If this is specified, any sequences created together with the unlogged table (for identity or serial columns) are also created as unlogged.

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.