User Tools

Site Tools


how_to:database_issues

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
how_to:database_issues [2011/10/21 10:42]
paul [Database corruption]
how_to:database_issues [2018/11/14 10:45] (current)
Line 1: Line 1:
 ======Database Issues====== ======Database Issues======
  
-VPOP3 version 3 and later use the [[http://​www.postgresql.org|PostgreSQL]]] database server. This is an open source enterprise level database server. Several VPOP3 problems are due to problems with the database.+VPOP3 version 3 and later use the [[http://​www.postgresql.org|PostgreSQL]]] database server. This is an open source enterprise level database server. Several VPOP3 problems are due to problems with the database ​service.
  
-=====Disk Caching===== +  * [[Database & Disk Caching]] 
- +  ​* ​[[Software conflicting with database]] 
-One key point is that, like all other high-end databases, PostgreSQL should not be run on a server with disk level [[http://​en.wikipedia.org/​wiki/​Cache#​Operation|write back caching]]. With write back caching, the disk will report data as being written to disk when it is still in the disk's cache memory. If the power then fails, this data is lost and can cause database corruption. As long as the database server really knows when the data has been written to disk, power failure will not cause database corruption as it can be automatically recovered after a power failure. +  * [[VPOP3DB/​PostgreSQL service not starting|VPOP3DB Service not starting]] 
- +  * [[Database Size]]
-High-end RAID controllers often contain a battery backed write cache (BBWC) - in this case, if the power fails, the cached data is held in battery powered memory until the power is restored. After power is restored the cached data will be written to disk. In this case, using write back caching through the BBWC is perfectly safe (as long as power is not left off for so long that the batteries run down). +
- +
-It is slightly less safe, but still usually OK, to have a UPS with automated shutdown of the PC. You still have to ensure that the PC is not just turned off without shutting down properly. +
- +
-If you don't have either of these facilities, then make sure you turn off write-back caching in Windows. +
- +
-Exactly how you turn off write-back caching depends on your version of Windows, and the disk controller. +
- +
-The instructions below are for Windows 7 with standard SATA disks +
- +
-  ​- Go to the '​Device Manager'​ in Windows. In Windows 7 this is via **Control Panel -> System -> Device Manager**\\ {{:​how_to:​device_manager.png|}} +
-  ​- Under **Disk Drives** find the hard disk that VPOP3 is installed on, and right-click it and choose **Properties**\\ {{:​how_to:​disk_properties.png|}} +
-  - Go to the **Policies** tab. Turn off **Enable write caching onthe device** and press **OK**\\ {{:​how_to:​write_caching.png|}} +
- +
-=====Possible Conflicting Software===== +
-We strongly recommend that virus scanners exclude the VPOP3\pgsql\data folder and all subfolders. If a virus scanner decides to quarantine a file from within this folder structure, that can cause VPOP3 not to run, and could lead to data loss or corruption. +
- +
-We also recommend that backup software does not backup this folder structure. If a backup program opens a file for backup, and then the database server needs it, it can cause problems, and crashes.+
  
 =====Database corruption===== =====Database corruption=====
-If VPOP3 won't start even though the process is running, then that is often because the PostgreSQL service is not running. If the PostgreSQL service won't start, or keeps crashing, then it is probably because the database is corrupted.+If VPOP3 won't start even though the process is running, then that is often because the PostgreSQL service is not running. If the PostgreSQL service won't start, or keeps crashing, then it is probably because the database is corrupted. ​In VPOP3 versions 3 and 4, the PostgreSQL service is usually called '​Postgres-VPOP3'​. In version 5 and later, the service is called '​VPOP3DB'​
  
   * [[Restore a database backup|Restoring a database]]   * [[Restore a database backup|Restoring a database]]
   * [[PostgreSQL server won't start due to damaged log files]] or missing pg_control file   * [[PostgreSQL server won't start due to damaged log files]] or missing pg_control file
   * [[Total Rebuild of Database]]   * [[Total Rebuild of Database]]
 +  * [[Repairing corrupted database]]
  
-=====PostgreSQL ​service ​not starting===== +Our chargeable support ​service ​will be able to help with this if necessaryNote that we cannot guarantee recovering ​data, as that will depend on how badly the database has been damaged, and when the latest good backup was.
-If the PostgreSQL service doesn'​t start, or starts and immediately stops, there are a couple of things you can do: +
- +
-  * Look in the VPOP3\pgsql\data\pg_log folder for a log file created at the time when you started VPOP3. If that is there, you can read that (using Notepad) and see if it reports any problems with files or '​relations'​. If so, it might be a problem ​with a damaged databaseSee below for other possible error messages in the PostgreSQL log files +
-  * Look in the Windows event log for entries relating to PostgreSQL +
- +
-You can also try starting the PostgreSQL service manually: +
- +
-From a command prompt, run "runas /​user:​postgres cmd" (password is "​vla8Pi4cluwrouDiERiU"​) +
- +
-Then  +
-  c: +
-  cd \vpop3\pgsql\bin +
-  pg_ctl -D "​C:​\vpop3\pgsql\data" start  +
-   +
-(If your VPOP3 is installed in a different directorychange the above paths as appropriate) +
- +
-See what response you get to the pg_ctl command +
- +
-====PostgreSQL Log Entries==== +
-If you get **FATAL** entries in the PostgreSQL log files (in **VPOP3\pgsql\data\pg_log**) then they may give more information about why PostgreSQL could not start. Some log entries we have come across are below. +
- +
-===no pg_hba.conf entry for host "::​1",​ user "​postgres", ​database ​"​postgres"​=== +
-This usually happens if the PC where VPOP3 is installed used to only have IPv4 installed, and has now had IPv6 added. The PostgreSQL configuration needs altering to support IPv6. +
- +
-Usually this only happens on Windows XP - earlier versions of Windows do not support IPv6, and later versions support IPv6 by default. Windows XP needs to have [[http://​support.microsoft.com/​kb/​2478747|IPv6 manually installed]] +
- +
-To fix this problem, go to the **VPOP3\pgsql\data** folder on the server and edit the **pg_hba.conf** file using a plain text editor such as Notepad. +
- +
-Near the bottom of that file you should see a section saying +
-<​code>​ +
-# TYPE  DATABASE ​       USER            CIDR-ADDRESS ​           METHOD +
- +
-# IPv4 local connections:​ +
-host    all             ​all ​            ​127.0.0.1/​32 ​           md5 +
-# IPv6 local connections:​ +
-#host    all             ​all ​            ::​1/​128 ​                md5 +
-</​code>​ +
- +
-Edit the last line to remove the # from the start, so it now says +
- +
-<​code>#​ TYPE  DATABASE ​       USER            CIDR-ADDRESS ​           METHOD +
- +
-# IPv4 local connections:​ +
-host    all             ​all ​            ​127.0.0.1/​32 ​           md5 +
-# IPv6 local connections:​ +
-host    all             ​all ​            ::​1/​128 ​                md5 +
-</​code>​ +
- +
-In some cases the last two lines may not exist, in which case you can simply copy those lines from the example above and paste them into your pg_hba.conf file+
  
-You will then need to restart the PostgreSQL service in Windows. Note that sometimes PostgreSQL cannot close down properly, and there will be unwanted '​postgres.exe'​ processes visible in Windows TaskManager on the Processes tab. If so, then end those processes manually, before restarting the service. 
how_to/database_issues.1319193768.txt.gz · Last modified: 2018/11/14 10:44 (external edit)