dropdb: database removal failed: ERROR: checkpoint request failed

Question

I only know the basics of using postgres db and have been using it for about a year without any major problems I couldn't get through with the docs or existing stackoverflow questions.

But now I have a problem where I keep getting the following error on my pg db on my mac. I tried to install an updated postgres using brew which installed successful, but I think in the process I created another problem. The error is when trying to start the db:

LOG:  database system shutdown was interrupted; last known up at 2015-10-04 11:04:08 EDT
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/6000090
LOG:  record with zero length at 0/6001990
LOG:  redo done at 0/6001960
LOG:  last completed transaction was at log time 2015-10-04 10:56:33.121103-04
FATAL:  could not open directory "pg_logical/snapshots": No such file or directory
LOG:  startup process (PID 12905) exited with exit code 1
LOG:  aborting startup due to startup process failure

So I followed the instructions here for using pg_resetxlog and then procceeded to try and "REINDEX, dump your database(s), re-initdb, and reload your databases" Per pg documentation for 9.4 with no successful results. Im just going in circles.

I can start over with the db but I'm unable to drop it and start over. If I run dropdb dbname I get

dropdb: could not connect to database template1: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

But I can't start the db because after running

pg_resetxlog -f /usr/local/var/postgres

And getting back "Transaction log reset" I run

pg_ctl start -D /usr/local/var/postgres

And get

pg_ctl: another server might be running; trying to start server anyway
server starting
FATAL:  lock file "postmaster.pid" already exists
HINT:  Is another postmaster (PID 13114) running in data directory "/usr/local/var/postgres"?

So I ran

ps auxwww | grep postgres

And got

Travis          13127   0.0  0.0  2432772    660 s000  S+   11:24AM   0:00.00 grep postgres
Travis          13120   0.0  0.0  2468112    604   ??  Ss   11:21AM   0:00.00 postgres: stats collector process       
Travis          13119   0.0  0.0  2604776   1444   ??  Ss   11:21AM   0:00.00 postgres: autovacuum launcher process       
Travis          13118   0.0  0.0  2604776    732   ??  Ss   11:21AM   0:00.00 postgres: wal writer process       
Travis          13117   0.0  0.0  2612968   1776   ??  Ss   11:21AM   0:00.05 postgres: writer process       
Travis          13116   0.0  0.0  2604776    756   ??  Ss   11:21AM   0:00.00 postgres: checkpointer process       
Travis          13114   0.0  0.2  2604776  14576   ??  S    11:21AM   0:00.02 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log

So I ran

kill 13114

Then

ps auxwww | grep postgres

And got

Travis          13139   0.0  0.0  2432772    652 s000  S+   11:25AM   0:00.00 grep postgres

Of course at this point if I try to start the db again I go in the same circle.

Now if I run

pg_resetxlog -f /usr/local/var/postgres

followed by

psql template1

And try to drop the db nothing happens.

It's a db for a rails project. So if I run

rake db:drop

I get another

PG::InternalError: ERROR:  checkpoint request failed

It also says there is a hint in the server log which I checked with

live:

tail -f /usr/local/var/postgres/server.log

Or:

tail /usr/local/var/postgres/server.log

And get

ERROR:  could not open directory "pg_logical/snapshots": No such file or directory
ERROR:  could not open directory "pg_logical/snapshots": No such file or directory
ERROR:  could not open directory "pg_logical/snapshots": No such file or directory
ERROR:  could not open directory "pg_logical/snapshots": No such file or directory
ERROR:  could not open directory "pg_logical/snapshots": No such file or directory
ERROR:  could not open directory "pg_logical/snapshots": No such file or directory
ERROR:  could not open directory "pg_logical/snapshots": No such file or directory
ERROR:  could not open directory "pg_logical/snapshots": No such file or directory
ERROR:  could not open directory "pg_logical/snapshots": No such file or directory
ERROR:  could not open directory "pg_logical/snapshots": No such file or directory

I'm not sure how to fix that or why it dosent exist anymore in the first place. Search results returned this but there was no solution showing the full path for manually creating the directory

At this point I'm so confused I think I'm making things worse.

I checked other db on my system and see I have the same problem for all databases on my machine now. Plus I always have 1 grep postgres pid running that i can't stop with kill 13498 or by restarting my mac.

How do i fix this without loosing all databases on my machine?

Brew info postgres shows:

postgresql: stable 9.4.4 (bottled), devel 9.5alpha2
Object-relational database system
https://www.postgresql.org/
Conflicts with: postgres-xc
/usr/local/Cellar/postgresql/9.4.1_1 (2996 files, 40M)
  Poured from bottle
/usr/local/Cellar/postgresql/9.4.4 (3014 files, 40M) *
  Poured from bottle
From: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/postgresql.rb
==> Dependencies
Required: openssl ✔, readline ✔
==> Options
--32-bit
    Build 32-bit only
--with-dtrace
    Build with DTrace support
--with-python
    Build with python support
--without-perl
    Build without Perl support
--without-tcl
    Build without Tcl support
--devel
    Install development version 9.5alpha2
==> Caveats
If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
  https://github.com/Homebrew/homebrew/issues/2510

To migrate existing data from a previous major version (pre-9.4) of PostgreSQL, see:
  https://www.postgresql.org/docs/9.4/static/upgrading.html

To have launchd start postgresql at login:
  ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
Then to load postgresql now:
  launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Or, if you don't want/need launchctl, you can just run:
  postgres -D /usr/local/var/postgres

I'm running yosemite 10.10.5

Are you using BDR? Or PostgreSQL 9.5 beta? Either way, Mac OS X seems to like to randomly delete empty directories, which the database system doesn't like much. — Craig Ringer, Oct 05 '15 at 02:17
Hey Craig, I'm using postgresql: stable 9.4.4 (bottled), devel 9.5alpha2 — jtlindsey, Oct 05 '15 at 17:09

score 6 · Accepted Answer · edited May 23 '17 at 12:14

After a few more hours of research I got things working again without loosing any of my databases.

From my other mac I found where the directory should be created that the user referenced in this post that needed to be created. I also created the other two missing directories:

mkdir /usr/local/var/postgres/pg_logical
mkdir /usr/local/var/postgres/pg_logical/mappings
mkdir /usr/local/var/postgres/pg_logical/snapshots

Next I ran the following for good measure

brew update

and

brew doctor

Then I installed brew services (info docs and article )

brew tap homebrew/services

Next i ran the following which i derived from hints here because in the process of troubleshooting/trial and error the "pg_resetxlog -f /usr/local/var/postgres" command started throwing errors.

brew services stop postgresql

Then I tried my usual

pg_ctl -D /usr/local/var/postgres start

And everything started working normally. Since brew services is installed now I can start the db with:

brew services start postgresql

I'm still not sure why restarting my mac didn't help after creating the directories. And I'm also not sure why/how/when those directories were removed. And I'm not sure what really created the problems to start with. But after doing what I've listed, everything is working.

Mac OS X seems to have the nasty habit of deleting empty directories when it feels like it. — Craig Ringer, Oct 06 '15 at 04:54

score 1 · Answer 2 · edited Mar 29 '16 at 06:59

1

Hi I have faced below error while deleting the database:

Error: checkpoint request failed

Need to restart the database and you can try to delete database.

sucessfully delete database.

edited Mar 29 '16 at 06:59

dhruv jadia

1,684
2
15
28

answered Mar 29 '16 at 06:15

Upendra

11
1

score 0 · Answer 3 · answered Aug 01 '17 at 21:38

If the disk space is full, you will not be able to drop any database and you will consistently get the 'Error: checkpoint request failed' message.

To manually delete the database follow these steps:
1. Shutdown Postgres (note that Postgres won't restart until you free up disk space)
2. Delete the database data directory (dddd) in /var/lib/postgresql/9.x/main/base/dddd
3. Restart Postgres (it should restart now that you have freed up space)
4. Execute 'drop database db_name' through psql console (to finish the cleanup)

dropdb: database removal failed: ERROR: checkpoint request failed

3 Answers3