Results 1 to 3 of 3

Thread: Postgresql

  1. #1

    Lightbulb Postgresql

    Hey,





    I need to change the location of the postgresql data that is in /var/lib/pgsql/data/. But when I change to /data in the configuration file, postgres does not start. Is there anything I can do?
    I just changed /var/lib/pgsql/data/postgresql.conf



    # systemctl status postgresql.service
    ● postgresql.service - PostgreSQL database server
    Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
    Active: failed (Result: timeout) since Mon 2021-05-10 20:24:15 -03; 18s ago
    Process: 6478 ExecStart=/usr/share/postgresql/postgresql-script start (code=killed, signal=TERM)


    May 10 20:22:45 celpmi-nmsapph postgresql-script[6478]: 2021-05-10 20:22:45.764 -03 [6487]LOG: listening on IPv6 address "::1", port 5432
    May 10 20:22:45 celpmi-nmsapph postgresql-script[6478]: 2021-05-10 20:22:45.764 -03 [6487]LOG: listening on IPv4 address "127.0.0.1", port 5432
    May 10 20:22:45 celpmi-nmsapph postgresql-script[6478]: 2021-05-10 20:22:45.765 -03 [6487]LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
    May 10 20:22:45 celpmi-nmsapph postgresql-script[6478]: 2021-05-10 20:22:45.767 -03 [6487]LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
    May 10 20:22:45 celpmi-nmsapph postgresql-script[6478]: 2021-05-10 20:22:45.791 -03 [6487]LOG: redirecting log output to logging collector process
    May 10 20:22:45 celpmi-nmsapph postgresql-script[6478]: 2021-05-10 20:22:45.791 -03 [6487]HINT: Future log output will appear in directory "log".
    May 10 20:24:15 celpmi-nmsapph systemd[1]: postgresql.service: Start operation timed out. Terminating.
    May 10 20:24:15 celpmi-nmsapph systemd[1]: Failed to start PostgreSQL database server.
    May 10 20:24:15 celpmi-nmsapph systemd[1]: postgresql.service: Unit entered failed state.
    May 10 20:24:15 celpmi-nmsapph systemd[1]: postgresql.service: Failed with result 'timeout'.





    tks

  2. #2
    Join Date
    Oct 2014
    Location
    Rotterdam
    Posts
    456

    Default Re: Postgresql

    Can you describe what you exactly changed in /var/lib/pgsql/data/postgresql.conf?

    With these kind of problems it is good to run the command systemd is executing by yourself from the command prompt and you log shows what the command is:

    Process: 6478 ExecStart=/usr/share/postgresql/postgresql-script start (code=killed, signal=TERM)

    So can you run "/usr/share/postgresql/postgresql-script start" and share the output?

    See also https://www.postgresql.org/docs/12/server-start.html

  3. #3

    Default Re: Postgresql

    postgres looks for the data directory in the home directory of the postgres system user. When you move the data dir and the conf therein, the postgres server won't even find the conf. Unless you tell it beforehand. The oldschool way is via env vars which is kind of what sysconfig files do for services. Check this

    Code:
     $ sudo systemctl cat postgresql 
    # /usr/lib/systemd/system/postgresql.service
    [Unit] 
    
    Description=PostgreSQL database server 
    After=syslog.target 
    After=network.target 
    
    [Service] 
    Type=forking 
    User=postgres 
    EnvironmentFile=-/etc/sysconfig/postgresql                <---------------------- this
    ExecStart=/usr/share/postgresql/postgresql-script start 
    ExecStop=/usr/share/postgresql/postgresql-script stop 
    ExecReload=/usr/share/postgresql/postgresql-script reload 
    
    # The server might be slow to stop, and that's fine. Don't kill it 
    SendSIGKILL=no 
    
    [Install] 
    WantedBy=multi-user.target
    
    
     $ head /etc/sysconfig/postgresql 
    ## Path:           Applications/PostgreSQL 
    ## Description:    The PostgreSQL Database System 
    ## Type:           string() 
    ## Default:        "~postgres/data" 
    ## ServiceRestart: postgresql 
    # 
    # In which directory should the PostgreSQL database reside? 
    # 
    POSTGRES_DATADIR="~postgres/data"
    
    
     $ grep postgres /etc/passwd 
    postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
    
    So this is how the actual location of pg data is resolved: postgres user home is /var/lib/pgsql and the postgres server expects its data dir below that. And the service unit environment file has not changed it. Change the location in /etc/sysconfig/postgresql to the new absolute path.

    There are other options though. I usually just symlink /var/lib/pgsql to /srv/pgsql. You could also consider bind mounting /data/pgsql on top of /var/lib/pgsql. Both require no changes to any config file

Tags for this Thread

Posting Permissions

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