3

I created a new PostgreSQL 9.5 database as follows:

[root@myhost pgsql]# sudo -u postgres initdb -D /var/lib/pgsql/test
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_CA.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/lib/pgsql/test ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/pgsql/test -l logfile start

If I then ask psql to list the databases, but my 'test' database is missing:

[root@myhost pgsql]# sudo -u postgres psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | 
 template0 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Can someone explain what's wrong? Do I have to register my new test database with PostgreSQL somehow?

I also tried to backup the new database but that too fails:

sudo -u postgres psql -d /var/lib/pgsql/test -c "SELECT pg_start_backup('test');"
psql: FATAL:  database "/var/lib/pgsql/test" does not exist
3

You have two commands confused:

  • initdb is used once to initialize a DB environment directory structure. That does not create a new database.

  • createdb is used for each DB you want to create.

When you ran your initdb you were just initializing the folder /var/lib/pgsql/test to hold ALL future databases, but you did not actually create ANY database.

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

Reference: initdb (PostgreSQL Documentation)

You did not specify if this is a test machine or planned for prod, but you probably should use the service command to run the initdb and after that sudo to the postgres user to run the createdb. Often in *nix systems the installer setups up PGDATA variable for you so you don't have to specify the directory. There are some installers that even run the initdb for you; you did not specify the OS, so no idea of how your OS / installer handles setup/installation.

So, you may do something like:

sudo service postgresql initdb
sudo su - postgres
createdb test
psql -c "\l test"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.