postgresql: creating database in a specified location

Question

I am trying to create a PG db in a specified location. According to the docs here, I must use the LOCATION flag. However, when I run CREATE DATABASE (from the pgsql CLI), I get the following warning:

WARNING:  LOCATION is not supported anymore
HINT:  Consider using tablespaces instead.

However, the PG documentation on TABLESPACES does not show how it can be used to create a database in a specific directory. What is the required syntax to do this?

You could lookup the CREATE TABLESPACE command in the fine manual here postgresql.org/docs/9.1/static/sql-createtablespace.html , and than do a create datbase : postgresql.org/docs/9.1/static/sql-createdatabase.html referring to that location — wildplasser
– wildplasser, Commented Mar 26, 2012 at 16:44

Karlson · Accepted Answer · 2021-11-02 13:26:20Z

20

You would need to do this in 2 steps:

Create Tablespace examples for which you can see in the link
Create Database

When you create tablespace you set it's location and then you can create multiple databases in the same tablespace if you choose to.

edited Nov 2, 2021 at 13:26

answered Mar 26, 2012 at 16:45

Karlson

3,0881 gold badge27 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Milen A. Radev · Accepted Answer · 2012-03-26 16:44:12Z

3

CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';

See the chapter about tablespaces in the manual.

edited Mar 26, 2012 at 16:44

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

answered Mar 26, 2012 at 16:42

johnshen64

3,8841 gold badge23 silver badges17 bronze badges

3 Comments

Homunculus Reticulli Over a year ago

Like I said, that reference does not show how to use tablespaces in a CREATE DATABASE command

Milen A. Radev Over a year ago

Actually it does.

johnshen64 Over a year ago

CREATE DATABASE name TABLESPACE = tablespace or you can set a default tablespace so that everything created will be in that table space like so: SET default_tablespace = tablespace;

Collectives™ on Stack Overflow

postgresql: creating database in a specified location

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related