Initdb error cannot be run as root

Step 1: I installed PostgreSQL using sudo apt-get install postgresql-9.1 as recommended on the PostgreSQL website

Step 2: I tried to run postgres. It’s not found. For whatever reason, the install doesn’t appear to add it to the path? So I had to manually add the line export PATH=$PATH:/usr/lib/postgresql/9.1/bin to the bottom of my ~/.profile. (Sidenote: Anybody know why this is necessary? Am I doing something wrong with the install? Everything else I’ve installed in Ubuntu «just works» without changing the $PATH…)

Step 3: I try running initdb /usr/local/var/postgres. Permission denied. I try running sudo initdb /usr/local/var/postgres. Result is sudo: initdb: command not found. How is this command not found? I just ran the damn thing! echo sudo $PATH shows the PostgreSQL directory in the path… what am I missing?

I’m a bit of a newbie in Linux, but these are the sorts of super-irritating problems I keep running into!

UPDATE: I believe it’s related to this question. However, running the command with sudo -i does not fix the problem. I just get: -bash: initdb: command not found. Great.

UPDATE: This seems even closer. So I added alias sudo='sudo env PATH=$PATH' to my .bashrc as instructed. Still doesn’t effin’ work! It looks like the alias isn’t working. When I run alias, I only show a single one. And yet my .bashrc is full of them… so something is wrong with those getting set up.

UPDATE: Since I’m using Ubuntu and RVM, RVM recommended that I set up the terminal to «Run command as login shell». Based on reading I did here, it seems that the .bashrc file isn’t read in a login shell, only profile. So I moved the alias line from .bashrc to .profile, so .profile now has this at the end:

export PATH="/usr/lib/postgresql/9.1/bin:$PATH"
alias sudo='sudo env PATH=$PATH'

… and it still doesn’t work. Running alias only shows an RVM alias, but not the sudo alias I tried to set up.

UPDATE From this site, I read about the precedence of dotfiles. It looks like .bash_profile comes before .profile. That being said, my PATH additions were done in .profile, and seemed to be loaded just fine, so why wasn’t the alias also working? Moving the alias into .bash_profile from .profile worked, however. Mystery. So then the alias command shows my new alias. I finally type in sudo initdb /usr/local/var/postgres, to be met with: initdb: cannot be run as root. Oh, really? Then why were you giving me permission errors?! So now I think the problem is that I just have to chown the folder, but still run initdb as my user rather than root.

UPDATE Running the command sudo chown myuser /usr/local/var/postgres/, and then running initdb afterward allowed the database to be initialized. Glad it was so obvious that the directory permissions needed to be set to myuser and not root. Incredible. Successful database init 4 hours later.

Sometimes PostgreSQL users get errors and warnings and they are unable to understand why. To cater to these situations, this blog will cover some common errors and warnings in PostgreSQL. In some cases, it is a user setting problem or query error, but in other cases, it can be a PostgreSQL bug. But, it is quite rare to be a PostgreSQL bug, and therefore it is really important to differentiate between user error and PostgreSQL bug. Here is a list of some common PostgreSQL errors, with symptoms and solutions.

1 – Is the PostgreSQL Server Running Locally and Accepting?

This usually occurs when the PostgreSQL server is not running, but in some cases, you can get a similar error even when the PostgreSQL server is still running. There are multiple ways to check whether the server is running or not, depending on the installation and operating system. Here are some steps you can perform to check:

The status of the PostgreSQL service shows it and all its subprocess processes are running. The second reason for the error could be the port number, as the default port of PostgreSQL is 5432. If PostgreSQL is configured to run on a different port, then the user needs to specify the port number (with some exceptions). Here is the way to check the port number:

2 – Initdb Cannot Be Run as Root

The initdb command is used to initialize the PostgreSQL cluster. Sometimes people try to use that by root, the user which can cause the said error. It’s very simple to switch the unprivileged user using us and initdb the cluster. You cannot “initdb” using superuser, so change the user that owns the server process and then do initdb. Or, you can use postgresql-12-setup to initialize the cluster.

3 – Initdb Failed Due to Directory ‘Invalid Permission’

The directory should have permission u=rwx (0700) or u=rwx,g=rx (0750) to perform initdb. Either you can initialize the cluster into another directory or change the permission of the directory to u=rwx (0700) or u=rwx,g=rx (0750).

4 – Object Permission

This error only happens when you create some object using one user and another user does have access to that object.

5 – Out of Disk Space Error

PostgreSQL initializes its cluster into $PGDATA. It is very important to keep an eye on that directory and free up some space before that drive runs out of it. There are some ways to optimize the situation, like:

• Free some space on the disk
• Point pg_wal to another disk, and in that case, all walls will generate to another disk, and data is distributed among multiple disks.
• Create a tablespace on another disk and create a table in that tablespace. You can divide your data between multiple disks.

6 – Replication Standby Issue

You are replicating a server to another which is not a copy of the original. You need to configure master replication and table a base backup using pg_basebackup and start the slave.

7 – The Server Terminated

This one is a critical error, and in that case, you need to identify the cause. If possible, generate the stack trace and consult your service provider.

8 – Out of Memory Issue

Every system has a limited amount of memory. When there is no memory left, PostgreSQL’s memory allocation functions start failing. Please check your configuration and limitations of your hardware.

9 – OOM Killer

When there is not enough memory left, the OOM killer invokes and kills the PostgreSQL process. Some people disable that process, but it is not recommended. You need to check the memory setting according to your hardware.

10 – Replication Archive Command Failure

This error appears when you have specified the destination directory for WAL in archive_command, which does not exist. You need to create /usr/local/wal/ file or specify the directory which does exist.

Our white paper “Why Choose PostgreSQL?” looks at the features and benefits of PostgreSQL and presents some practical usage examples. We also examine how PostgreSQL can be useful for companies looking to migrate from Oracle.

Download PDF

As said by @Harald Nordgren, you need sum up all the RUN commands in one command, if possible.

Along with that there are couple of things which are causing the failures

1) adding «postgres» user:

«adduser» expects the additional parameters to be configured when you add a user, as you have mentioned in the comments like asking for password and so. So you need to modify the command to disable the arguments as well as the password like below:

adduser postgres --gecos '' --disabled-login 

2) executing postgress using root user

When you execute the command «su postgres«, it executes with root user. But whereas we have changed the permissions in the above command «chown postgres /usr/local/pgsql/data»

For this you need a execute the command as «postgres» user which can be enabled by adding USER in dockerfile.

Finally your Dockerfile looks something like this:

FROM ubuntu

RUN apt-get update && apt-get install gcc zlib1g-dev libreadline6-dev apt-utils make -y

RUN mkdir -p /tmp/downloads

ADD https://ftp.postgresql.org/pub/source/v9.6.6/postgresql-9.6.6.tar.gz /tmp/downloads

RUN cd /tmp/downloads && tar -zxf postgresql-9.6.6.tar.gz

RUN cd /tmp/downloads/postgresql-9.6.6 &&
    make configure &&
    ./configure &&
    make &&
    su &&
    make install &&
    adduser postgres --gecos '' --disabled-login &&
    mkdir /usr/local/pgsql/data &&
    chown postgres /usr/local/pgsql/data

USER postgres

#use below command only if it is necessary, it is similar to "cd" linux command
WORKDIR /tmp/downloads/postgresql-9.6.6
RUN /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data 

With this I am able to create a Docker image and tested as well. Adding more information which may be useful.

# docker run -it postgres:2.0 /bin/bash
postgres@8354d83023f9:/tmp/downloads/postgresql-9.6.6$ ps -eaf
UID        PID  PPID  C STIME TTY          TIME CMD
postgres     1     0  0 10:58 ?        00:00:00 /bin/bash
postgres     9     1  0 10:59 ?        00:00:00 ps -eaf


postgres@8354d83023f9:/tmp/downloads/postgresql-9.6.6$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
server starting

postgres@8354d83023f9:/tmp/downloads/postgresql-9.6.6$ ps -eaf
UID        PID  PPID  C STIME TTY          TIME CMD
postgres     1     0  0 10:58 ?        00:00:00 /bin/bash
postgres    13     1  0 10:59 ?        00:00:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
postgres    15    13  0 10:59 ?        00:00:00 postgres: checkpointer process   
postgres    16    13  0 10:59 ?        00:00:00 postgres: writer process   
postgres    17    13  0 10:59 ?        00:00:00 postgres: wal writer process   
postgres    18    13  0 10:59 ?        00:00:00 postgres: autovacuum launcher process   
postgres    19    13  0 10:59 ?        00:00:00 postgres: stats collector process   
postgres    20     1  0 10:59 ?        00:00:00 ps -eaf


postgres@8354d83023f9:/tmp/downloads/postgresql-9.6.6$ /usr/local/pgsql/bin/createdb test


postgres@8354d83023f9:/tmp/downloads/postgresql-9.6.6$ /usr/local/pgsql/bin/psql test
psql (9.6.6)
Type "help" for help.

test=# 
test=# 

There are other ways to build this docker image but this way I am able to.

Initdb error cannot be run as root

initdb [ option . ] [ —pgdata | -D ] directory

Description

initdb creates a new PostgreSQL database cluster. A database cluster is a collection of databases that are managed by a single server instance.

Creating a database cluster consists of creating the directories in which the database data will live, generating the shared catalog tables (tables that belong to the whole cluster rather than to any particular database), and creating the postgres , template1 , and template0 databases. The postgres database is a default database meant for use by users, utilities and third party applications. template1 and template0 are meant as source databases to be copied by later CREATE DATABASE commands. template0 should never be modified, but you can add objects to template1 , which by default will be copied into databases created later. See Section 23.3 for more details.

Although initdb will attempt to create the specified data directory, it might not have permission if the parent directory of the desired data directory is root-owned. To initialize in such a setup, create an empty data directory as root, then use chown to assign ownership of that directory to the database user account, then su to become the database user to run initdb .

initdb must be run as the user that will own the server process, because the server needs to have access to the files and directories that initdb creates. Since the server cannot be run as root, you must not run initdb as root either. (It will in fact refuse to do so.)

For security reasons the new cluster created by initdb will only be accessible by the cluster owner by default. The —allow-group-access option allows any user in the same group as the cluster owner to read files in the cluster. This is useful for performing backups as a non-privileged user.

initdb initializes the database cluster’s default locale and character set encoding. These can also be set separately for each database when it is created. initdb determines those settings for the template databases, which will serve as the default for all other databases. By default, initdb uses the locale provider libc , takes the locale settings from the environment, and determines the encoding from the locale settings. This is almost always sufficient, unless there are special requirements.

To choose a different locale for the cluster, use the option —locale . There are also individual options —lc-* (see below) to set values for the individual locale categories. Note that inconsistent settings for different locale categories can give nonsensical results, so this should be used with care.

Alternatively, the ICU library can be used to provide locale services. (Again, this only sets the default for subsequently created databases.) To select this option, specify —locale-provider=icu . To choose the specific ICU locale ID to apply, use the option —icu-locale . Note that for implementation reasons and to support legacy code, initdb will still select and initialize libc locale settings when the ICU locale provider is used.

When initdb runs, it will print out the locale settings it has chosen. If you have complex requirements or specified multiple options, it is advisable to check that the result matches what was intended.

More details about locale settings can be found in Section 24.1.

To alter the default encoding, use the —encoding . More details can be found in Section 24.3.

Options

This option specifies the default authentication method for local users used in pg_hba.conf ( host and local lines). See Section 21.1 for an overview of valid values.

initdb will prepopulate pg_hba.conf entries using the specified authentication method for non-replication as well as replication connections.

Do not use trust unless you trust all local users on your system. trust is the default for ease of installation.

This option specifies the authentication method for local users via TCP/IP connections used in pg_hba.conf ( host lines).

This option specifies the authentication method for local users via Unix-domain socket connections used in pg_hba.conf ( local lines).

-D directory
—pgdata= directory

This option specifies the directory where the database cluster should be stored. This is the only information required by initdb , but you can avoid writing it by setting the PGDATA environment variable, which can be convenient since the database server ( postgres ) can find the database directory later by the same variable.

-E encoding
—encoding= encoding

Selects the encoding of the template databases. This will also be the default encoding of any database you create later, unless you override it then. The default is derived from the locale, if the libc locale provider is used, or UTF8 if the ICU locale provider is used. The character sets supported by the PostgreSQL server are described in Section 24.3.1.

Allows users in the same group as the cluster owner to read all cluster files created by initdb . This option is ignored on Windows as it does not support POSIX -style group permissions.

Specifies the ICU locale ID, if the ICU locale provider is used.

Use checksums on data pages to help detect corruption by the I/O system that would otherwise be silent. Enabling checksums may incur a noticeable performance penalty. If set, checksums are calculated for all objects, in all databases. All checksum failures will be reported in the pg_stat_database view. See Section 30.2 for details.

Sets the default locale for the database cluster. If this option is not specified, the locale is inherited from the environment that initdb runs in. Locale support is described in Section 24.1.

—lc-collate= locale
—lc-ctype= locale
—lc-messages= locale
—lc-monetary= locale
—lc-numeric= locale
—lc-time= locale

Like —locale , but only sets the locale in the specified category.

Equivalent to —locale=C .

This option sets the locale provider for databases created in the new cluster. It can be overridden in the CREATE DATABASE command when new databases are subsequently created. The default is libc .

By default, initdb will wait for all files to be written safely to disk. This option causes initdb to return without waiting, which is faster, but means that a subsequent operating system crash can leave the data directory corrupt. Generally, this option is useful for testing, but should not be used when creating a production installation.

By default, initdb will write instructions for how to start the cluster at the end of its output. This option causes those instructions to be left out. This is primarily intended for use by tools that wrap initdb in platform-specific behavior, where those instructions are likely to be incorrect.

Makes initdb read the database superuser’s password from a file. The first line of the file is taken as the password.

Safely write all database files to disk and exit. This does not perform any of the normal initdb operations. Generally, this option is useful for ensuring reliable recovery after changing fsync from off to on .

-T config
—text-search-config= config

Sets the default text search configuration. See default_text_search_config for further information.

-U username
—username= username

Selects the user name of the database superuser. This defaults to the name of the effective user running initdb . It is really not important what the superuser’s name is, but one might choose to keep the customary name postgres , even if the operating system user’s name is different.

Makes initdb prompt for a password to give the database superuser. If you don’t plan on using password authentication, this is not important. Otherwise you won’t be able to use password authentication until you have a password set up.

-X directory
—waldir= directory

This option specifies the directory where the write-ahead log should be stored.

Set the WAL segment size, in megabytes. This is the size of each individual file in the WAL log. The default size is 16 megabytes. The value must be a power of 2 between 1 and 1024 (megabytes). This option can only be set during initialization, and cannot be changed later.

It may be useful to adjust this size to control the granularity of WAL log shipping or archiving. Also, in databases with a high volume of WAL, the sheer number of WAL files per directory can become a performance and management problem. Increasing the WAL file size will reduce the number of WAL files.

Other, less commonly used, options are also available:

Print debugging output from the bootstrap backend and a few other messages of lesser interest for the general public. The bootstrap backend is the program initdb uses to create the catalog tables. This option generates a tremendous amount of extremely boring output.

Run the bootstrap backend with the debug_discard_caches=1 option. This takes a very long time and is only of use for deep debugging.

Specifies where initdb should find its input files to initialize the database cluster. This is normally not necessary. You will be told if you need to specify their location explicitly.

By default, when initdb determines that an error prevented it from completely creating the database cluster, it removes any files it might have created before discovering that it cannot finish the job. This option inhibits tidying-up and is thus useful for debugging.

Print the initdb version and exit.

Show help about initdb command line arguments, and exit.

Environment

Specifies the directory where the database cluster is to be stored; can be overridden using the -D option.

Specifies whether to use color in diagnostic messages. Possible values are always , auto and never .

Specifies the default time zone of the created database cluster. The value should be a full time zone name (see Section 8.5.3).

This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 34.15).

Notes

initdb can also be invoked via pg_ctl initdb .

See Also

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Copyright © 1996-2023 The PostgreSQL Global Development Group

Источник

initdb

Название

Синтаксис

Описание

Команда initdb создаёт новый кластер баз данных PostgreSQL . Кластер — это коллекция баз данных под управлением единого экземпляра сервера.

Инициализация кластера базы данных заключается в создании каталогов для хранения данных, формировании общих системных таблиц (относящихся ко всему кластеру, а не к какой-либо базе) и создании баз данных template1 и postgres. Впоследствии все новые базы создаются на основе шаблона template1 (все дополнения, установленные в template1 автоматически копируются в каждую новую базу данных). База postgres используется пользователями, утилитами и сторонними приложениями по умолчанию.

При попытке создать каталог для хранения данных initdb может столкнуться с нехваткой прав доступа, если этот каталог принадлежит суперпользователю root. В таком случае необходимо назначить пользователя базы данных владельцем этого каталога при помощи chown. Затем выполнить su для смены пользователя и дальнейшего выполнения initdb.

Команда initdb должна выполняться от имени пользователя, под которым будет запускаться сервер, так как ему необходим полный доступ к файлам и каталогам, создаваемым initdb. Сервер не может запускаться от имени суперпользователя, поэтому выполнение команды initdb от его лица будет отклонено.

initdb инициализирует локали и кодировки баз данных кластера, которые будут использоваться по умолчанию. Кодировка, порядок сортировки ( LC_COLLATE), классы наборов символов ( LC_CTYPE, например, заглавные, строчные буквы, цифры) могут устанавливаться раздельно при создании новой базы данных. initdb определяет параметры локали для шаблона template1, которые будут применяться по умолчанию для новых баз.

Чтобы изменить порядок сортировки по умолчанию или классы наборов символов, используются параметры —lc-collate и —lc-ctype. Порядок сортировки, отличающийся от C или POSIX, оказывает влияние на производительность. Поэтому необходимо тщательно выбирать необходимую и достаточную локаль при выполнении initdb.

Другие категории локали можно изменить и после старта сервера. Также можно использовать параметр —locale, чтобы задать локаль для всех категорий одновременно, включая порядок сортировки и классы наборов символов. Значения локалей сервера ( lc_*) можно вывести командой SHOW ALL. Узнать об этом больше можно в Разделе 22.1.

Для изменения кодировки по умолчанию используется параметр —encoding. Узнать об этом больше можно в Разделе 22.3.

Параметры

Параметр указывает на метод аутентификации локальных пользователей, используемый в файле pg_hba.conf (строки host и local). Не используйте trust, если не можете доверять всем системным пользователям ОС. trust используется по умолчанию для облегчения процесса установки. —auth-host= authmethod

Параметр указывает метод аутентификации для локальных пользователей, подключающихся по TCP/IP, используемый в pg_hba.conf (строки host). —auth-local= authmethod

Параметр указывает метод аутентификации локальных пользователей подключающихся по доменным Unix-сокетам, используемый в pg_hba.conf (строки local). -D каталог
—pgdata= каталог

Параметр указывает каталог хранения данных кластера. Это единственный обязательный параметр для команды initdb. При этом его можно указать в переменной окружения PGDATA, что будет удобным при дальнейшем использовании ( postgres обращается к этой же переменной). -E кодировка
—encoding= кодировка

Устанавливает кодировку шаблона и новых баз данных по умолчанию, если не указать иное при их создании. По умолчанию устанавливается исходя из указанной локали, и далее, если не удалось определить, выбирается SQL_ASCII. Кодировки, поддерживаемые сервером PostgreSQL , описаны в Подразделе 22.3.1. -k
—data-checksums

Указывает на необходимость проверки системой ввода/вывода контрольных сумм страниц для обнаружения повреждённых данных, так как по умолчанию проверка не производится. Включение проверки может в значительной мере оказать влияние на производительность. Устанавливается на этапе развёртывания кластера, и далее не может быть изменена. Когда проверка включена, производится вычисление контрольных сумм для всех объектов всех баз данных кластера. —locale= локаль

Устанавливает локаль кластера по умолчанию. Если флаг не указан, локаль устанавливается согласно окружению, в котором исполняется команда initdb. Поддерживаемые локали описаны в Разделе 22.1. —lc-collate= локаль
—lc-ctype= локаль
—lc-messages= локаль
—lc-monetary= локаль
—lc-numeric= локаль
—lc-time= локаль

Аналогично —locale устанавливает необходимую локаль, но в заданной категории. —no-locale

Аналогично флагу —locale=C. -N
—nosync

По умолчанию initdb ожидает завершения записи всех файлов в дисковую подсистему. Данный параметр приводит initdb к немедленному завершению, что быстрее, но не гарантирует успешности создания каталога хранения данных. Обычно он используется при тестировании, для производственной среды он не подходит. —pwfile= имя_файла

Принуждает initdb читать пароль суперпользователя базы данных из файла, первая строка которого используется в качестве пароля. -S
—sync-only

Безопасно записывает все файлы базы на диск и останавливается. Другие операции initdb при этом не выполняются. -T CFG
—text-search-config= CFG

Устанавливает конфигурацию текстового поиска по умолчанию. За дополнительными сведениями обратитесь к default_text_search_config. -U имя_пользователя
—username= имя_пользователя

Устанавливает имя суперпользователя базы данных. По умолчанию используется имя пользователя ОС, запустившего initdb. По факту, само по себе имя суперпользователя базы данных не важно, но этот параметр позволяет оставить привычное postgres , если имя пользователя ОС другое. -W
—pwprompt

Принуждает initdb запросить пароль, устанавливаемый для суперпользователя базы данных. Это не важно, если не планируется аутентификация по паролю. В ином случае, использовать её невозможно, пока пароль не задан. -X каталог
—xlogdir= каталог

Флаг указывает каталог для хранения журналов транзакций.

Другие реже используемые параметры описаны здесь:

Распечатывает отладочный вывод и некоторую дополнительную информацию при начальной работе загрузчика. Загрузчик это приложение initdb, используемое для создания каталога таблиц. -L каталог

Указывает initdb, где необходимо искать входные файлы для развёртывания кластера. Обычно это не требуется. Приложение само запросит эти данные, если будет необходимо. -n
—noclean

По умолчанию, при выявлении ошибки на этапе развёртывания кластера, initdb удаляет все файлы, которые к тому моменту были созданы. Параметр предотвращает очистку файлов для целей отладки.

Выводит версию initdb и останавливается. -?
—help

Показывает помощь по аргументам команды initdb и останавливается.

Переменные окружения

Указывает каталог хранения данных кластера, можно изменить параметром -D. TZ

Указывает часовой пояс кластера по умолчанию. Значение — это полное имя часового пояса (см. Подраздел 8.5.3).

Эта утилита, как и большинство других утилит PostgreSQL , также использует переменные среды, поддерживаемые libpq (см. Раздел 31.14).

Замечания

initdb можно выполнить командой pg_ctl initdb.

Источник

{"@timestamp":"2020-04-02T14:23:59.744Z","@version":"1","message":"Extracting Postgres...","logger_name":"io.zonky.test.db.postgres.embedded.EmbeddedPostgres","thread_name":"prefetching-1","level":"INFO","level_value":20000}
2020-04-02 14:24:01.840  INFO [-,,,] 80 --- [  prefetching-1] i.z.t.d.p.embedded.EmbeddedPostgres      : Postgres binaries at /tmp/embedded-pg/PG-a8263178df5b3591feca6b619939c2bb
{"@timestamp":"2020-04-02T14:24:01.840Z","@version":"1","message":"Postgres binaries at /tmp/embedded-pg/PG-a8263178df5b3591feca6b619939c2bb","logger_name":"io.zonky.test.db.postgres.embedded.EmbeddedPostgres","thread_name":"prefetching-1","level":"INFO","level_value":20000}
2020-04-02 14:24:01.851  INFO [-,,,] 80 --- [initdb:pid(129)] i.z.t.d.p.embedded.EmbeddedPostgres      : initdb: cannot be run as root
{"@timestamp":"2020-04-02T14:24:01.851Z","@version":"1","message":"initdb: cannot be run as root","logger_name":"io.zonky.test.db.postgres.embedded.EmbeddedPostgres","thread_name":"initdb:pid(129)","level":"INFO","level_value":20000}
2020-04-02 14:24:01.852  INFO [-,,,] 80 --- [initdb:pid(129)] i.z.t.d.p.embedded.EmbeddedPostgres      : Please log in (using, e.g., "su") as the (unprivileged) user that will
{"@timestamp":"2020-04-02T14:24:01.852Z","@version":"1","message":"Please log in (using, e.g., "su") as the (unprivileged) user that will","logger_name":"io.zonky.test.db.postgres.embedded.EmbeddedPostgres","thread_name":"initdb:pid(129)","level":"INFO","level_value":20000}
2020-04-02 14:24:01.853  INFO [-,,,] 80 --- [initdb:pid(129)] i.z.t.d.p.embedded.EmbeddedPostgres      : own the server process.
{"@timestamp":"2020-04-02T14:24:01.853Z","@version":"1","message":"own the server process.","logger_name":"io.zonky.test.db.postgres.embedded.EmbeddedPostgres","thread_name":"initdb:pid(129)","level":"INFO","level_value":20000}
2020-04-02 14:24:01.863  INFO [-,,,] 80 --- [initdb:pid(133)] i.z.t.d.p.embedded.EmbeddedPostgres      : initdb: cannot be run as root
2020-04-02 14:24:01.862 ERROR [-,,,] 80 --- [    Test worker] wayEmbeddedPostgresDataSourceFactoryBean : Unexpected error during the initialization of embedded database
{"@timestamp":"2020-04-02T14:24:01.863Z","@version":"1","message":"initdb: cannot be run as root","logger_name":"io.zonky.test.db.postgres.embedded.EmbeddedPostgres","thread_name":"initdb:pid(133)","level":"INFO","level_value":20000}

java.util.concurrent.CompletionException: com.google.common.util.concurrent.UncheckedExecutionException: java.lang.IllegalStateException: Process [/tmp/embedded-pg/PG-a8263178df5b3591feca6b619939c2bb/bin/initdb, -A, trust, -U, postgres, -D, /tmp/epg6740838134234363154, -E, UTF-8, --pwfile=postgres.password, --lc-monetary=cs_CZ.UTF-8, --lc-numeric=cs_CZ.UTF-8, --lc-collate=cs_CZ.UTF-8, --username=postgres] failed
	at io.zonky.test.db.flyway.DefaultFlywayDataSourceContext.lambda$reload$0(DefaultFlywayDataSourceContext.java:113)
	at java.util.concurrent.CompletableFuture.uniApply(CompletableFuture.java:616)
	at java.util.concurrent.CompletableFuture$UniApply.tryFire(CompletableFuture.java:591)
	at java.util.concurrent.CompletableFuture$Completion.run(CompletableFuture.java:4

Sometimes PostgreSQL users get errors and warnings and they are unable to understand why. To cater to these situations, this blog will cover some common errors and warnings in PostgreSQL. In some cases, it is a user setting problem or query error, but in other cases, it can be a PostgreSQL bug. But, it is quite rare to be a PostgreSQL bug, and therefore it is really important to differentiate between user error and PostgreSQL bug. Here is a list of some common PostgreSQL errors, with symptoms and solutions.

1 – Is the PostgreSQL Server Running Locally and Accepting?

$ psql postgres 
psql: 
error: could not connect to server: could not connect to server: No such file or directory 
Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

This usually occurs when the PostgreSQL server is not running, but in some cases, you can get a similar error even when the PostgreSQL server is still running. There are multiple ways to check whether the server is running or not, depending on the installation and operating system. Here are some steps you can perform to check:

sudo service postgresql-12 status
Redirecting to /bin/systemctl status postgresql-12.service
postgresql-12.service - PostgreSQL 12 database server
    Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; disabled; vendor preset: disabled)
    Active: active (running) since Sun 2020-05-31 23:55:39 UTC; 8s ago
        Docs: https://www.postgresql.org/docs/12/static/
    Process: 32204 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 32209 (postmaster)
CGroup: /system.slice/postgresql-12.service
    ├─32209 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
    ├─32211 postgres: logger
    ├─32213 postgres: checkpointer
    ├─32214 postgres: background writer
    ├─32215 postgres: walwriter
    ├─32216 postgres: autovacuum launcher
    ├─32217 postgres: stats collector
    └─32218 postgres: logical replication launcher

The status of the PostgreSQL service shows it and all its subprocess processes are running. The second reason for the error could be the port number, as the default port of PostgreSQL is 5432. If PostgreSQL is configured to run on a different port, then the user needs to specify the port number (with some exceptions). Here is the way to check the port number:

sudo service postgresql-12 status | grep port
Redirecting to /bin/systemctl status postgresql-12.service
LOG:  listening on IPv6 address "::1", port 5432
listening on IPv4 address "127.0.0.1", port 5432

2 – Initdb Cannot Be Run as Root

$ initdb -D data initdb: error: cannot be run as root Please log in (using, e.g., "su") as the (unprivileged) user that will own the server process.

The initdb command is used to initialize the PostgreSQL cluster. Sometimes people try to use that by root, the user which can cause the said error. It’s very simple to switch the unprivileged user using us and initdb the cluster. You cannot “initdb” using superuser, so change the user that owns the server process and then do initdb. Or, you can use postgresql-12-setup to initialize the cluster.

sudo ./postgresql-12-setup initdb

3 – Initdb Failed Due to Directory ‘Invalid Permission’

$ initdb -D data running bootstrap script ... 
FATAL:  data directory "data" has invalid permissions [1885] 
DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750). 
child process exited with exit code 1 initdb: removing data directory "data"

The directory should have permission u=rwx (0700) or u=rwx,g=rx (0750) to perform initdb. Either you can initialize the cluster into another directory or change the permission of the directory to u=rwx (0700) or u=rwx,g=rx (0750).

4 – Object Permission

$ SELECT * FROM TEST; 
ERROR:  permission denied for table test [1788] 
STATEMENT:  select * from test; 
ERROR:  permission denied for table test

This error only happens when you create some object using one user and another user does have access to that object.

5 – Out of Disk Space Error

ERROR: could not extend file "base/30122/331821":
No space left on device
HINT: Check free disk space.

PostgreSQL initializes its cluster into $PGDATA. It is very important to keep an eye on that directory and free up some space before that drive runs out of it. There are some ways to optimize the situation, like:

• Free some space on the disk
• Point pg_wal to another disk, and in that case, all walls will generate to another disk, and data is distributed among multiple disks.
• Create a tablespace on another disk and create a table in that tablespace. You can divide your data between multiple disks.

6 – Replication Standby Issue

FATAL:  database system identifier differs between the primary and standby [20595]
DETAIL:  The primary's identifier is 6832398539310516067, the standby's identifier is 6832397799517112074.

You are replicating a server to another which is not a copy of the original. You need to configure master replication and table a base backup using pg_basebackup and start the slave.

7 – The Server Terminated

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed

This one is a critical error, and in that case, you need to identify the cause. If possible, generate the stack trace and consult your service provider.

8 – Out of Memory Issue

ERROR: out of memory 2020-05-08 
DETAIL: Failed on request of size 1880.

Every system has a limited amount of memory. When there is no memory left, PostgreSQL’s memory allocation functions start failing. Please check your configuration and limitations of your hardware.

9 – OOM Killer

Out of memory: Kill process 1766 (postmaster) score 890 or sacrifice child
Killed process 1766, UID 26,(postmaster) total-vm: 24384508 kB, anon-rss:14376288kB, file-rss:138616kB

When there is not enough memory left, the OOM killer invokes and kills the PostgreSQL process. Some people disable that process, but it is not recommended. You need to check the memory setting according to your hardware.

10 – Replication A rchive Command Failure

cp: cannot create regular file '/usr/local/wal/000000010000000000000001': No such file or directory
LOG:  archive command failed with exit code 1
DETAIL:  The failed archive command was: cp pg_wal/000000010000000000000001 /usr/local/pgsql-logical/wal/000000010000000000000001

This error appears when you have specified the destination directory for WAL in archive_command, which does not exist. You need to create /usr/local/wal/ file or specify the directory which does exist.

Step 1: I installed PostgreSQL using sudo apt-get install postgresql-9.1 as recommended on the PostgreSQL website

Step 2: I tried to run postgres. It’s not found. For whatever reason, the install doesn’t appear to add it to the path? So I had to manually add the line export PATH=$PATH:/usr/lib/postgresql/9.1/bin to the bottom of my ~/.profile. (Sidenote: Anybody know why this is necessary? Am I doing something wrong with the install? Everything else I’ve installed in Ubuntu «just works» without changing the $PATH…)

Step 3: I try running initdb /usr/local/var/postgres. Permission denied. I try running sudo initdb /usr/local/var/postgres. Result is sudo: initdb: command not found. How is this command not found? I just ran the damn thing! echo sudo $PATH shows the PostgreSQL directory in the path… what am I missing?

I’m a bit of a newbie in Linux, but these are the sorts of super-irritating problems I keep running into!

UPDATE: I believe it’s related to this question. However, running the command with sudo -i does not fix the problem. I just get: -bash: initdb: command not found. Great.

UPDATE: This seems even closer. So I added alias sudo='sudo env PATH=$PATH' to my .bashrc as instructed. Still doesn’t effin’ work! It looks like the alias isn’t working. When I run alias, I only show a single one. And yet my .bashrc is full of them… so something is wrong with those getting set up.

UPDATE: Since I’m using Ubuntu and RVM, RVM recommended that I set up the terminal to «Run command as login shell». Based on reading I did here, it seems that the .bashrc file isn’t read in a login shell, only profile. So I moved the alias line from .bashrc to .profile, so .profile now has this at the end:

export PATH="/usr/lib/postgresql/9.1/bin:$PATH"
alias sudo='sudo env PATH=$PATH'

… and it still doesn’t work. Running alias only shows an RVM alias, but not the sudo alias I tried to set up.

UPDATE From this site, I read about the precedence of dotfiles. It looks like .bash_profile comes before .profile. That being said, my PATH additions were done in .profile, and seemed to be loaded just fine, so why wasn’t the alias also working? Moving the alias into .bash_profile from .profile worked, however. Mystery. So then the alias command shows my new alias. I finally type in sudo initdb /usr/local/var/postgres, to be met with: initdb: cannot be run as root. Oh, really? Then why were you giving me permission errors?! So now I think the problem is that I just have to chown the folder, but still run initdb as my user rather than root.

UPDATE Running the command sudo chown myuser /usr/local/var/postgres/, and then running initdb afterward allowed the database to be initialized. Glad it was so obvious that the directory permissions needed to be set to myuser and not root. Incredible. Successful database init 4 hours later.