Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2012
    Posts
    8

    Unable to restart PostgreSQL server after changing data directory

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,817
    Any messages in the postgres log file?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Oct 2012
    Posts
    8
    no sir startup log file is empty..plz help

  4. #4
    Join Date
    Nov 2003
    Posts
    2,817
    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?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Oct 2012
    Posts
    8
    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

  6. #6
    Join Date
    Nov 2003
    Posts
    2,817
    What happens if you start it manually using pg_ctl?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Oct 2012
    Posts
    8
    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

  8. #8
    Join Date
    Dec 2012
    Posts
    32
    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

  9. #9
    Join Date
    Dec 2012
    Posts
    32
    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.

  10. #10
    Join Date
    Oct 2012
    Posts
    8
    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

  11. #11
    Join Date
    Dec 2012
    Posts
    32
    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)

  12. #12
    Join Date
    Oct 2012
    Posts
    8
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •