Unable to restart PostgreSQL server after changing data directory

akash94thakur · #1 (**permalink**) 01-29-13, 11:34

Hi all,

I have got stuck with this issue.On my production server the disk on which data directory was located got filled completely so i planed to move data directory to new disk mounted on the server.I took following steps for this
1)stopped db server
2)copied data directory to new location.
3)changed pgdata parameter in script at /etc/init.d
4) restarted the server

the status of startup comes ok but server doesn't get started and no errors are coming.

i also tried to start server manually using -D option but all in vein
Permission on new directory are appropriate.(correct).
I did same thing on other servers where this thing is working fine but on one of the server this issue is occurring ..please help me

If i am reverting the changes and using old data directory then server is running fine again.help me

shammat · #2 (**permalink**) 01-29-13, 12:27

Any messages in the postgres log file?

akash94thakur · #3 (**permalink**) 01-29-13, 12:30

no sir startup log file is empty..plz help

shammat · #4 (**permalink**) 01-29-13, 12:41

What is the "startup log file"? I'm talking about the Postgres logfile.

I'm almost 100% certain that it is a permission problem.

Btw: What does plz mean? What have German zip codes to do with this?

akash94thakur · #5 (**permalink**) 01-29-13, 12:47

no there is no permission issues.. owner and group of all the files is postgres and permissions are rwx------ yes the startup logfile is empty. and server log file will only be created once server gets started.now i have deleted the data directory and trying to copy again the old data directory on this mounted location.
can ther be any issue with mounted disk

shammat · #6 (**permalink**) 01-29-13, 12:58

What happens if you start it manually using pg_ctl?

akash94thakur · #7 (**permalink**) 01-29-13, 13:04

when i start it like
pg_ctl -D path/to/datadirectory start ,then a prompt comes server starting...
but when i check status then it comes no server running and evn when i grep postmaster no process related to postgres are shown

when i start it using postmaster -D in foreground then process starts and ends like this
EXIT [1] again no server starts

bibjet · #8 (**permalink**) 01-29-13, 17:39

Are you very sure that "postgres" is the owner of the data directory and all subdirectories and files?

you can execute the following command to be sure:
( Please rename "/my/new/datadir" with your own new data directory.
Assuming the user is "postgres" and the groupname is also "postgres".)

Code:

chown -R postgres:postgres /my/new/datadir

The command doesn't return any message if it was successful.
See also:

Code:

man chown

The owner and permissions on my data directory ("/var/lib/pgsql/data") are:

Code:

rtj13:~ # ls -al /var/lib/pgsql/data
total 76
drwx------ 14 postgres postgres  4096 Jan 29 21:29 .
drwxr-x---  3 postgres postgres    91 Jan 23 01:11 ..
-rw-------  1 postgres postgres     4 Nov 18 15:43 PG_VERSION
drwx------  8 postgres postgres    78 Jan 14 02:32 base
drwx------  2 postgres postgres  4096 Jan 29 21:30 global
drwx------  2 postgres postgres    17 Nov 18 15:43 pg_clog
-rw-------  1 postgres postgres  4365 Dec 15 01:56 pg_hba.conf
-rw-------  1 postgres postgres  1636 Nov 18 15:43 pg_ident.conf
drwx------  2 postgres postgres 12288 Jan 29 21:29 pg_log
drwx------  4 postgres postgres    34 Nov 18 15:43 pg_multixact
drwx------  2 postgres postgres    17 Jan 29 21:29 pg_notify
drwx------  2 postgres postgres     6 Nov 18 15:43 pg_serial
drwx------  2 postgres postgres    24 Jan 29 23:18 pg_stat_tmp
drwx------  2 postgres postgres    17 Nov 18 15:43 pg_subtrans
drwx------  2 postgres postgres     6 Nov 18 15:43 pg_tblspc
drwx------  2 postgres postgres     6 Nov 18 15:43 pg_twophase
drwx------  3 postgres postgres  4096 Dec 15 02:15 pg_xlog
-rw-------  1 postgres postgres 19198 Dec 15 02:09 postgresql.conf
-rw-------  1 postgres postgres   246 Dec 15 02:09 postmaster.log
-rw-------  1 postgres postgres    62 Jan 29 21:29 postmaster.opts
-rw-------  1 postgres postgres    68 Jan 29 21:29 postmaster.pid

bibjet · #9 (**permalink**) 01-29-13, 17:49

As you can see in my last reply there is a file

Code:

postmaster.pid

in the data directory.
Of course this PID-file could be elsewhere if configured in postgresql.conf or as option of pg_ctl (?).

This file should only exists if the server is running.
It will be deleted when the server during shutting down regulary.

The server will prevented from start if this file exists in your new data direcory.

So please delete this file if you are sure that the server is down.

akash94thakur · #10 (**permalink**) 01-30-13, 00:05

hi bibjet,
Yes permissions are accurate and this pid file is not getting created in my new data directory.old pid file is not there in new data directory

bibjet · #11 (**permalink**) 01-30-13, 17:06

After you checked the owner and permissions there are following reason that prevents postgreSQL from start:

1. CORRUPTED FILES
Perhaps it's not possible to start postgresql because of the full filesystem when using the old data directory.
Also after copying the data directory one ore more files could be corrupt because postmaster wasn't able to store data correct into the file(s) when the filesystem was full.
Solution: Use your backup.

2. CONFIG FILE IN postgresql.conf POINTS TO THE OLD DATA DIR
Please check that no file in /new/datadir/postgresql.conf points to the old data directory.
The most important parameters are:

Code:

#data_directory = 'ConfigDir'           # use data in another directory
                                        # (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file
                                        # (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file 
                                        # (change requires restart)

3. MISSPELLED VARIABLE NAME OR DATA DIRECTORY IN /etc/init/postgresql
Please check right spelling of changed variable names ore content in /etc/init/postgresql

4. STARTING POSTGRESQL WITH THE WRONG COMMAND NAME OR USER
Please start postgreSQL like the system does it at boot time (depending of your distribution).
You didn't wrote the name of your distribution.
On my system (openSuSE 12.2) I have to execute

Code:

/etc/init.d/postgresql start

as user root
or

Code:

systemctl start postgresql.service

The data directory is stored as a variable in an other file ("/etc/sysconfig/postgresql") to avoid changing the init script "/etc/init/postgresql" and loosing the changes after an online update.

Please reply what you did and put the commands and messages as CODE - tags into your post.
To be sure that the owner and permissions are correct please post also the output of

Code:

ls -al /you/new/datadir

Please post the last lines of the youngest logfile (in directory <datadir>/pg_log if not otherwise configured)

akash94thakur · #12 (**permalink**) 02-01-13, 10:34

Hi all,
This is resolved now as some idiot from client side had done entered in bash_profile for pg_data due to which new new value of pg_data was not getting picked.

Thank-you all for your replies.

Internet Services

Web Development

Digital Marketing

Consumer Tech