Windows postgresql data directory

В дополнение к вышеупомянутому postgresql.conf , Postgres Pro обрабатывает два редактируемых вручную файла конфигурации, в которых настраивается аутентификация клиентов (их использование рассматривается в Главе 19). По умолчанию все три файла конфигурации размещаются в каталоге данных кластера БД. Параметры, описанные в этом разделе, позволяют разместить их и в любом другом месте. (Это позволяет упростить администрирование, в частности, выполнять резервное копирование этих файлов обычно проще, когда они хранятся отдельно.)

Задаёт каталог, в котором хранятся данные. Этот параметр можно задать только при запуске сервера. config_file ( string )

Задаёт основной файл конфигурации сервера (его стандартное имя — postgresql.conf ). Этот параметр можно задать только в командной строке postgres . hba_file ( string )

Задаёт файл конфигурации для аутентификации по сетевым узлам (его стандартное имя — pg_hba.conf ). Этот параметр можно задать только при старте сервера. ident_file ( string )

Задаёт файл конфигурации для сопоставлений имён пользователей (его стандартное имя — pg_ident.conf ). Этот параметр можно задать только при запуске сервера. См. также Раздел 19.2. external_pid_file ( string )

Задаёт имя дополнительного файла с идентификатором процесса (PID), который будет создавать сервер для использования программами администрирования. Этот параметр можно задать только при запуске сервера.

При стандартной установке ни один из этих параметров не задаётся явно. Вместо них задаётся только каталог данных, аргументом командной строки -D или переменной окружения PGDATA , и все необходимые файлы конфигурации загружаются из этого каталога.

Если вы хотите разместить файлы конфигурации не в каталоге данных, то аргумент командной строки postgres -D или переменная окружения PGDATA должны указывать на каталог, содержащий файлы конфигурации, а в postgresql.conf (или в командной строке) должен задаваться параметр data_directory , указывающий, где фактически находятся данные. Учтите, что data_directory переопределяет путь, задаваемый в -D или PGDATA как путь каталога данных, но не расположение файлов конфигурации.

При желании вы можете задать имена и пути файлов конфигурации по отдельности, воспользовавшись параметрами config_file , hba_file и/или ident_file . Параметр config_file можно задать только в командной строке postgres , тогда как остальные можно задать и в основном файле конфигурации. Если явно заданы все три эти параметра плюс data_directory , то задавать -D или PGDATA не нужно.

Во всех этих параметрах относительный путь должен задаваться от каталога, в котором запускается postgres .

Change the default PGDATA directory on Windows

PostgreSQL for Windows installs the PGDATA directory by default into «C:\Program Files\PostgreSQL\some version\data». This mini-HOWTO explains how to change the default PGDATA directory to another location.

Contents

Step 1: Stop The PostgreSQL Service

Close all application that are currently connected to your database, then go to Windows Services Management and stop the PostgreSQL service:

Start->Settings->Control Panel->Administrative Tools->Services

You should check the task manager to see if any postgresql.exe instances are still running. If so, DO NOT TERMINATE them, instead close all applications that are still connected to the database. Sometimes services like webservers keep persistent connections. In this case you also should stop these services.

Step 2: Change Registry Values

Start the Windows Registry Editor (regedit.exe) and navigate to: «HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-some version».

Double click on «ImagePath» and change the directory after the «–D» option to your new location. If the path to your new location contains spaces, you should then enclose it with quotes.

Step 3: Move the data folder to a new the location

Move your current PGDATA directory to the directory you just specified in Step 2. Please note that you should do this as Administrator on server machines. You should also check user permissions in the new directory to ensure the username under which your PostgreSQL instance runs has permissions to do so in the new directory, otherwise PostgreSQL will not start.

Step 4: Restart The PostgreSQL Service

Before you can start your database you must close and re-open the Windows Services Management window. This refreshes the path you just changed in the registry without the need to restart your system.

After closing and re-opening the Services Management window, you should check the «path to executable» information of the PostgreSQL Database service. The path after «–D» option should be pointing to your new directory now.

Now that the PGDATA directory has been re-located and the service startup parameter in the registry has been modified, you can go ahead and start the service. [[Category::Administration]]

Windows 10 is always FATAL: data directory «/var/lib/postgresql/data» has wrong ownership #435

Comments

ayuthmang commented Apr 23, 2018 •

On Windows 10 Pro is always failed while docker-compose up, otherwise on mac is work perfectly.

Here are some my docker-compose file:

and there are some logs.

The text was updated successfully, but these errors were encountered:

maiorano84 commented May 9, 2018

I have this same issue only on Windows. Everywhere else is fine.

The only way I was able to get around this was through a named volume mount:

tianon commented May 16, 2018

Unfortunately, this is likely docker/for-win#445, and there’s not really anything we can do to fix it directly in the image. You might be able to work around it by messing with —user ( user: in the compose YAML), but there are some caveats to that noted in https://hub.docker.com/_/postgres/.

matthewfieger commented Nov 4, 2019

Are there any updates on this issue? Here is a simple case to produce this error:

I tried 1) changing the location of the data folder 2) running as root and 3) changing the ownership of the data folder. I can’t use a named volume as others have suggested because I need to mount the data folder to a specific location on my server.

yami12376 commented Nov 5, 2019

I have the same issue running:
docker run —name some-postgres -v E:\tmp\postgresBaza:/var/lib/postgresql/data -d postgres:9.6.15

I get from logs:

FATAL: data directory «/var/lib/postgresql/data» has wrong ownership

How to move a PostgreSQL 12 data directory

The following are an easy to follow steps to change the data directory in PostgreSQL 12.

FIND THE LOCATION OF POSTGRES CONFIG FILE

In your DB console execute the following query:

The result will show you the path to your postgresql.conf file, something like this:

FIND THE LOCATION OF POSTGRES DATA DIRECTORY

CHANGE THE PATH FROM OLD TO NEW DATA DIRECTORY

In my case, I want to change the data directory location from the current /var/lib/pgsql/12/data, to a new directory /home/postgres/data

I have turned off the PostgreSQL first, by running:

Once the PostgreSQL server is down, I have moved data folder from /var/lib/pgsql/12/ to /home/postgres/.

Once done, log in to your Linux server and open the PostgreSQL config file (postgresql.conf) on the suggested path (in step one). Then, find the ‘data_directory’ directive and change it to a new data folder, like this:

You may also want to edit your systemd service. To do so, go to /lib/systemd/system and find postgresql-12.service file (this can possibly be called differently on your system). Once found, open and edit the ‘Environment=PGDATA’ directive:

Once completed, save the file, refresh systemctl and start the PostgreSQL:

PostgreSQL: Change or Move Default Data Directory in Windows

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing basic steps to move or change the default Windows Data Directory of the PostgreSQL.

When we install PostgreSQL on Windows operating system, by default It store in “C:\Program Files\PostgreSQL\.\Data\”

Always best practice is to store database data in separate drive instead of the system drive.

Here, It is step-by-step:

Step 1:

Go to Windows Service, and Stop running PostgreSQL service.
To Close all your running transactions and applications which are currently connected to PostgreSQL.

Step 2:

If you are ready with your new location path, copy old data directory and paste in the new location path.

Step 3:

Right click on the newly created folder and make sure that It has all type permissions for local postgres system user.

Step 4:

Open Windows Registry Editor and go to this path “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\postgresql”.

Double click on “ImagePath” and change the default path of the Data Directory after “-D”

Step 5:

Close all the open window and start the PostgreSQL Service.

Please visit other related articles.

Anvesh,
Thanks a lot for very informative port. I was trying to follow steps and run into problem. Hope you can help to clarify. I have PostgreSQL 9.5 on windows and image-path (in regedit) shows startup line as C:\PostgreSQL\pg95\pgservice.exe “//RS//PostgreSQL 9.5 Server” , so there is no explicit data-directory to change. How would you recommend to change data-directory in my setting?
Thanks in advance.
Alex

I am not sure, need to check. But may be you can make a new entry in regedit.

We successfully did it without any issues.

Does this also restrict another local user from accessing the database that a particular user created in the data folder? If not then how can that be possible?

Leave a Reply Cancel reply

Anvesh Patel

Email Subscription !

About Me!

I’m Anvesh Patel, a Database Engineer certified by Oracle and IBM. I’m working as a Database Architect, Database Optimizer, Database Administrator, Database Developer. Providing the best articles and solutions for different problems in the best manner through my blogs is my passion. I have more than six years of experience with various RDBMS products like MSSQL Server, PostgreSQL, MySQL, Greenplum and currently learning and doing research on BIGData and NoSQL technology. — Hyderabad, India.

About DBRND !

This is a personal blog (www.dbrnd.com).

Any views or opinions represented in this blog are personal and belong solely to the blog owner and do not represent those of people, institutions or organizations that the owner may or may not be associated with in professional or personal capacity, unless explicitly stated.

Feel free to challenge me, disagree with me, or tell me I’m completely nuts in the comments section of each blog entry, but I reserve the right to delete any comment for any reason whatsoever (abusive, profane, rude, or anonymous comments) — so keep it polite.

The content of this website is protected by copyright. No portion of this website may be copied or replicated in any form without the written consent of the website owner.