0

Due to some craziness on a server I am looking after, I have a huge database that needs to be truncated (44GB despite being a simple site). However, I can't start the postgres service as there is not enough room on the server. How can I delete records, vacuum or do anything else that will make space without being able to start the pgsql service?

  • I get the message to check the logs which says (paraphrased) that there is not enough storage. – Daniel Casserly Nov 23 '16 at 21:43
  • 1
    Please give the un-paraphrased error message. – jjanes Nov 23 '16 at 22:14
4

Try start with --single-mode to truncate (PG document)

Usage

postgres --single -D /usr/local/pgsql/data other-options my_database

Note

The postgres command can also be called in single-user mode. The primary use for this mode is during bootstrapping by initdb. Sometimes it is used for debugging or disaster recovery; note that running a single-user server is not truly suitable for debugging the server, since no realistic interprocess communication and locking will happen. When invoked in single-user mode from the shell, the user can enter queries and the results will be printed to the screen, but in a form that is more useful for developers than end users. In the single-user mode, the session user will be set to the user with ID 1, and implicit superuser powers are granted to this user. This user does not actually have to exist, so the single-user mode can be used to manually recover from certain kinds of accidental damage to the system catalogs.

Note that the single-user mode server does not provide sophisticated line-editing features (no command history, for example). Single-User mode also does not do any background processing, like automatic checkpoints.

Example

postgres@xxx-yyy$ ./bin/postgres --single  -D data/ postgres

PostgreSQL stand-alone backend 9.6.0
backend> truncate t1 ;
2016-11-23 07:53:17.340 UTC: proc=18481,user=,db=,host=LOG:  statement: truncate t1 ;
backend> vacuum full t1 ;
2016-11-23 07:56:44.541 UTC: proc=19135,user=,db=,host=LOG:  statement: vacuum full t1 ;

In terms of postgres process related to this mode

ps -ef | grep postgres
postgres 19135  4510  0 07:56 pts/1    00:00:00 /opt/PostgreSQL/9.6/bin/postgres --single -D data/ postgres

If --single-mode does not help you, please add more space (as @ypercube mentioned), then start postgres server and truncate data.

0

When people run their servers out of storage, there are often some expendable files which can either be deleted, or moved someplace else. The easiest way to get back up and running is to find these and delete/move them. For example, overly-verbose logging files, either of PostgreSQL or the OS, or backup or archive files which are kept needlessly or in the wrong place.

If you can find your PostgreSQL data directory, you can run Linux command like du -Sh $PGDATA| sort -h to see where your storage is going. Or run it a few directories above that one, in case the real culprit is something else on your system and not PostgreSQL at all.

Where is your pg_xlog going? The same filesystem that is already full, or has it been symlinked to a different partition?

What are your settings for WAL archival?

If there is no expendable/movable data on that disk, then you are in a tight spot. You can't delete or vacuum anything through PostgreSQL until you can get the server to start. And even then, DELETE will not directly free up space to return to the OS. DELETE followed by VACUUM might return space to the OS if you are lucky, but it can also use a lot of additional space before it gets to the point where it can return any. The safest and fastest way to free up space to return to the OS is to DROP some large indexes. They can always be rebuilt later once you have the situation under control. If you can't do that, next best is TRUNCATE entire tables, if there are any tables which are expendable. But again, until you get your server to start, you can't do any of those things.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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