Postgresql linux database location

How do you customize the location of Postgres databases on a Linux server?

I am using CentOS 7.3 as a guest VM from Oracle VirtualBox. The host is Windows 7 and I have a physical USB stick (aka flash drive) to house the data directory for Postgres. I can use a USB stick as a mounted directory in Linux. I can read and write files to it.

I expect to be able to have Postgres databases on my USB stick. But I cannot get Postgres databases on my USB stick. I installed Postgres on the Linux VM.

To change the default data directory of Postgres, I followed these directions.

If you do not have time to go to the link, I simply installed Postgres with these two commands:

then I ran these two commands:

Afterward I try to start the Postgres service, I get an error.

Here is the command that I try (as root):

systemctl start postgresql

Here is the error:

Job for postgresql.service failed because the control process exited with error code. See «systemctl status postgresql.service» and «journalctl -xe» for detail.

I tried systemctl status postgresql.service and I found this:

Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled vendorpreset: disabled)

Active: failed (Result exit-code). Process . ExecStartPre=/usr/bin/postgresql-check-db-dir $ (code=exited, status=1/FAILURE)

. failed to start PostgreSQL. . Unit postgresql.service. . postgresql.service failed

I used journalctl -xe but that did not tell me anything meaningful.

To change the default data directory of Postgres, I tried this:

failed to find PGDATA setting in —pgdata=/mnt/mar/data.service

How do I get Postgres installed with a customized data directory? I need it to be in a the «/mnt/» directory. I want to create all my databases on a USB stick.

Источник

How do I find the path to pg_hba.conf from the shell?

I would like to obtain the path to pg_hba.conf from the shell. The path varies between versions of PostgreSQL. For instance, for 8.4 and 9.1:

I have tried the pg_config command, but it does not seem to include this information.

This is so that I can use a uniform command for opening pg_hba.conf and other PostgreSQL configuration files for editing.

7 Answers 7

pg_config is for compliation information, to help extensions and client programs compile and link against PostgreSQL. It knows nothing about the active PostgreSQL instance(s) on the machine, only the binaries.

pg_hba.conf can appear in many other places depending on how Pg was installed. The standard location is pg_hba.conf within the data_directory of the database (which could be in /home , /var/lib/pgsql , /var/lib/postgresql/[version]/ , /opt/postgres/ , etc etc etc) but users and packagers can put it wherever they like. Unfortunately.

The only valid ways find pg_hba.conf is to ask a running PostgreSQL instance where it’s pg_hba.conf is, or ask the sysadmin where it is. You can’t even rely on asking where the datadir is and parsing postgresql.conf because an init script might passed a param like -c hba_file=/some/other/path when starting Pg.

What you want to do is ask PostgreSQL:

This command must be run on a superuser session, so for shell scripting you might write something like:

and set the environment variables PGUSER , PGDATABASE , etc to ensure that the connection is right.

Yes, this is somewhat of a chicken-and-egg problem, in that if the user can’t connect (say, after screwing up editing pg_hba.conf ) you can’t find pg_hba.conf in order to fix it.

Another option is to look at the ps command’s output and see if the postmaster data directory argument -D is visible there, e.g.

since pg_hba.conf will be inside the data directory (unless you’re on Debian/Ubuntu or some derivative and using their packages).

If you’re targeting specifically Ubuntu systems with PostgreSQL installed from Debian/Ubuntu packages it gets a little easier. You don’t have to deal with hand-compiled-from-source Pg that someone’s initdb ‘d a datadir for in their home dir, or an EnterpriseDB Pg install in /opt, etc. You can ask pg_wrapper , the Debian/Ubuntu multi-version Pg manager, where PostgreSQL is using the pg_lsclusters command from pg_wrapper .

Источник

default location of postgresql when installing through apt-get

When you install postgresql on 14.04, it sticks the main server program postgres at:

the data directory where all the database clusters will be stored at:

and the configuration file at:

Now I can understand why postgresql.conf and other configuration files are stored in /etc/postgresql/9.3/main. After all, /etc is where configuration files are stored in a linux system.

However, why place the database storage area in /var/lib? I can understand /var, since that is the place for nonstatic data and databases are nonstatic. But why /var/lib in particular?

Further, I believe that /bin is for programs required for boot. /usr/bin is for programs included in the distribution. and /usr/local/bin should be for programs not included in the distribution but available for system-wide use. And thus since postgresql is intended for system-wide use, it should be available in /usr/local/bin. Yet, they place it in /usr/lib, which I have no idea why.

Why do I ask this question? Because without order and structure, it is difficult to remember the location of programs you use everyday.

1 Answer 1

In the Filesystem Hierarchy Standard, `/var/lib/ is stated as (in italic the most important part):

This hierarchy holds state information pertaining to an application or the system. State information is data that programs modify while they run, and that pertains to one specific host. Users must never need to modify files in /var/lib to configure a package’s operation.

State information is generally used to preserve the condition of an application (or a group of inter-related applications) between invocations and between different instances of the same application. State information should generally remain valid after a reboot, should not be logging output, and should not be spooled data.

An application (or a group of inter-related applications) must use a subdirectory of /var/lib for its data. There is one required subdirectory, /var/lib/misc, which is intended for state files that don’t need a subdirectory; the other subdirectories should only be present if the application in question is included in the distribution.

/var/lib/ is the location that must be used for all distribution packaging support. Different distributions may use different names, of course.

In short: /var/lib/ is for data that is used locally.

So it makes perfect sense to put a database’s data into /var/lib// directory but. the FHS is a standard created mostly for use by distributions. As a user you are free to put your data wherever you want and it is mostly a matter of opinion.

You are misunderstanding the word «local». /usr/local/bin/ is not for system software but for your own software (basically anything with «local» in must never be touched by the system. As explained by FHS:

The /usr/local hierarchy is for use by the system administrator when installing software locally. It needs to be safe from being overwritten when the system software is updated. It may be used for programs and data that are shareable amongst a group of hosts, but not found in /usr. Locally installed software must be placed within /usr/local rather than /usr unless it is being installed to replace or upgrade software in /usr.

An executable installed from system software should never go to anything local.

/usr/lib includes object files, libraries, and internal binaries that are not intended to be executed directly by users or shell scripts. Applications may use a single subdirectory under /usr/lib. If an application uses a subdirectory, all architecture-dependent data exclusively used by the application must be placed within that subdirectory.

postgressql is probably a daemon started at boot? If so it makes sense to put it here. You are not suppose to use the command yourself but start a service. The files in /usr/lib/ tend to have their own user and group and/or a daemon that restricts access to /var/lib (only mysqld can access /var/lib/mysql/ for instance; this will be the same for postgressql)

Источник

Where Postgres database files are saved in ubuntu?

How can I find where Postgres 8.x database files are saved in Ubuntu 10.04 file system?

2 Answers 2

In the postgres prompt, just execute this query:

Here’s how I located the directory of my Postgres database files in Ubuntu:

Run the command below in your terminal to switch user to postgres user:

It will request for the postgres user password which you set up when you were setting up PostgreSQL on your machine.

Next, you will need to login into the psql terminal/prompt. This can be accomplished by running the code below:

It will also request for the postgres password which you set up when you were setting up PostgreSQL on your machine.

If you are successfully logged in, you will see a prompt similar to this:

At this point you will execute the query below to display the directory where Postgres Database files are stored on your Linux macchine:

This should display an output similar to this:

You can now locate the Postgres Database files by navigating the directory that was displayed.

However, the PostgreSQL configuration directory in Ubuntu is located in /etc/postgresql/10/main . Take note that 10 is the version of my PostgreSQL installation on my server. Your version might be 9.5 , 11 or 12 or any other version. Run the command psql —version to confirm your PostgreSQL version.

Run the command below to navigate to the directory:

Источник

PostgreSQL database default location on Linux

What is the default directory where PostgreSQL will keep all databases on Linux?

8 Answers 8

The «directory where postgresql will keep all databases» (and configuration) is called «data directory» and corresponds to what PostgreSQL calls (a little confusingly) a «database cluster», which is not related to distributed computing, it just means a group of databases and related objects managed by a PostgreSQL server.

The location of the data directory depends on the distribution. If you install from source, the default is /usr/local/pgsql/data :

In file system terms, a database cluster will be a single directory under which all data will be stored. We call this the data directory or data area. It is completely up to you where you choose to store your data. There is no default, although locations such as /usr/local/pgsql/data or /var/lib/pgsql/data are popular. (ref)

Besides, an instance of a running PostgreSQL server is associated to one cluster; the location of its data directory can be passed to the server daemon («postmaster» or «postgres») in the -D command line option, or by the PGDATA environment variable (usually in the scope of the running user, typically postgres ). You can usually see the running server with something like this:

Note that it is possible, though not very frequent, to run two instances of the same PostgreSQL server (same binaries, different processes) that serve different «clusters» (data directories). Of course, each instance would listen on its own TCP/IP port.

Источник