User Tools

Site Tools


how_to:postgresql_server_won_t_start_due_to_damaged_log_files

PostgreSQL server won't start due to damaged log files

If the PostgreSQL server won't start at all, because its transaction logs are damaged, you will probably need to recreate the database store totally.

To rebuild the database:

  1. Open a command prompt and find the VPOP\pgsql folder
  2. Rename the data folder to data_old
  3. Run
    bin\initdb -D <VPOP3folder>\pgsql\data -U postgres -W -A md5
  4. When it prompts for a password, type in the password you want for the PostgreSQL superuser - we use pgsqlpass. If you choose to use something different, make sure you remember what it is!
  5. Type
    copy data_old\postgresql.conf data
    copy data_old\vpop3db.conf data
    copy data_old\vpop3dbuser.conf data

    to copy the database server configuration to the new location. You may be prompted if you want to overwrite the existing file. If so, confirm the overwrite.

  6. Set the permissions on the new Data folder. In Windows Explorer, right-click the VPOP3\pgsql\DATA folder and choose 'Properties' → 'Security'. Check that the 'vpop3postgres' user ('postgres' user in VPOP3 versions 3 and 4) has 'Full control' of the folder.
  7. Start the PostgreSQL service in Windows
  8. From the command prompt in vpop3\pgsql\bin run
    createuser -S -D -R -U postgres -P vpop3

    At the 'Enter Password for new role' and 'Enter it again' prompts type vpop3pass. At the 'Password:' prompt type pgsqlpass (or whatever password you defined in the step 4 above)

Once you're happy everything is working OK, you can delete the data_old folder.

If you wish, we can remote into your server and try to recover the data for you, but there will be a £75 charge for this. You can email us or phone on +44 1484 855800 to arrange this.

Possible Causes

The most common causes of this type of corruption are some third party software accessing the pgsql\data folder files - usually a virus scanner or backup or file synchronisation program. Backup programs using the Windows 'volume snapshot' (VSS) service are OK to access the pgsql\data folder, other backup programs should always be excluded from the pgsql\data folder.

Note that if you use a backup program which doesn't use VSS, even if it does a copy of the pgsql\data folder without causing any problems, that copy will not be usable, so there is no point doing it. See here for how to backup the database correctly

The only files in the pgsql\data folder which should ever be accessed by anything other than PostgreSQL itself are the contents of the pg_log folder (NOT the pg_xlog/pg_clog folders!) or the *.conf files.

Possible alternative solution

You may be able to use the pg_resetxlog.exe program to create new transaction logs and control files which are 'just good enough' to get PostgreSQL started again, then you can backup the database before doing the following steps. (Do not use pg_resetxlog.exe, then continue using the database without doing a full backup/rebuild/restore, corruption may be hidden, and cause bigger problems in the future). See the pg_resetxlog instructions for details on how to use that utility.

how_to/postgresql_server_won_t_start_due_to_damaged_log_files.txt · Last modified: 2018/11/14 10:45 by 127.0.0.1