Problems with Createdb in Postgres

Postgresql not creating db with “createdb” as superuser, yet not outputting errors

createdb is a command line utility which you can run from bash and not from psql.
To create a database from psql, use the create database statement like so:

create database [databasename];

Note: be sure to always end your SQL statements with ;

Problems with createdb in postgres

I would assume that on the machines where the user "giulio" is already known, you executed initdb with exactly this user making him the DB superuser. A quote from inidb(1) (emphasis mine):

--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 sys‐
tem user's name is different.

On the other machines I assume you did execute initdb with another user, hopefully using postgres.

In order to get back on the standard track I propose, that you delete the database cluster on the machines where "giulio" is the superuser and setup a new database cluster using the standard postgres user. Then add another user "giulio". This will avoid more confusion down the road as some scripts/programs expect a superuser account named postgres.

Why is the CREATE DATABASE command not working?

You have to terminate SQL commands with ;.

In your case:

CREATE DATABASE test;

Createdb does not create a db in postgres: no error message

createdb is a shell command line command. At the psql client prompt use the create database command

When the prompt is -# it is still waiting for the command termination, a semicolon. Enter Ctrl C to escape that.

Error while using createdb command postgresql on windows 10

So, basically, I figured the solution myself. I am just posting it here because mostly answers are available for Linux and not Windows. So, if a windows user has a similar problem, maybe this answer could help them.

So, the first thing is, if you need to open psql, use the command:

psql -U postgres

and then enter the password you used while installing PostgreSQL. Now, if you wish to do something similar to what I tried, what I mean is to use createdb command in the terminal itself, then you will have to create a new user using the same username as you do for your PC, like in my case, it is aryan.

(For example: C:\Users\aryan\).

I followed instructions from this website.

I personally used pgAdmin 4 to do it, you could also use the SQL commands themselves.

After doing everything, when I used the createdb command directly from the terminal/powershell, it asked my the password which I had used to create the other user( with the same username as my system/pc) using pgAdmin 4. That's it. This helped me out.

Cannot createdb in postgresql

At first, you installed PostgreSQL version 9.3, then you uninstalled it and installed version 9.4. But some old files stayed there and PostgreSQL 9.4 is not compatible with them. If you don't need any data of 9.3 version then you can remove them and initialize new settings for 9.4 version:

rm -rf /usr/local/var/postgres
initdb /usr/local/var/postgres -E utf8

postgresql createdb and CREATE DATABASE yield a non-empty database. what the fork?

Summarizing from the docs template0 is essentially a clean, virgin system database, whereas template1 serves as a blue print for any new database created with the createdb command or create database from a psql prompt (there is no effective difference).

It is probable that you have some tables lurking in template1, which is why they keep reappearing on createdb. You can solve this by dropping template1 and recreating it from template0.

createdb -T template0 template1 

The template1 database can be extremely useful. I use Postgis a lot, so I have all of the functions and tables related to that installed in template1, so any new database I create is immediately spatially enabled.

EDIT. As noted in docs, but worth emphasizing, to delete tempate1 you need to have pg_database.datistemplate = false set.

problem with create database if not exist via postgres backup and restore tool

Should work by using postgres://postgres:1234@127.0.0.1:9195/postgres and adding -C. Obviously test on throw away instance. This will connect to postgres database DROP DATABASE IF EXISTS mydb; , then CREATE DATABASE mydb, connect to mydb and then restore the database objects.

To demonstrate:

\l test_db
                       List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges 
------+-------+----------+---------+-------+-------------------
(0 rows)

pg_restore -d postgres -c -C -U postgres test_db.out 
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4734; 1262 1170111 DATABASE test_db postgres
pg_restore: error: could not execute query: ERROR:  database "test_db" does not exist
Command was: DROP DATABASE test_db;
pg_restore: warning: errors ignored on restore: 1

 \l test_db
                               List of databases
  Name   |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
---------+----------+----------+-------------+-------------+-------------------
 test_db | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)

pg_restore -d postgres -c -C -U postgres test_db.out

\l test_db
                               List of databases
  Name   |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
---------+----------+----------+-------------+-------------+-------------------
 test_db | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)

---

Related Topics