postgresql setup 1: create a database cluster
There are a lot of things I like about PostgreSQL: It does a great job conforming to the SQL 92 and SQL 99 standards, while at the same time it supports more than a dozen procedural languages. It's robust and scalable. It grows with your needs. It's well documented.
But there's one thing I don't like: It's a pain to set up. Well, maybe pain is an overstatement. But PostgreSQL does not just work out of the box; it takes a little effort to get it set up.
I recently set up PostgreSQL on a new machine running Fedora, and it took a little research to find all the necessary steps. I'm going to try to pull everything together here.
Part 1: Create a database cluster
A database cluster is the collection of databases to be managed by a database server. The initdb command sets up a cluster with two default databases you'll need to have: postgres, which is used by many third party apps, and template1, which will be the template for the databases you create.
The cluster must have a home directory. This can be located anywhere on the machine; popular choices, according to the PostgreSQL documentation notes that /usr/local/pgsql/data and /var/lib/pgsql/data. You may have to create the directory first, then chown it to user postgres.
Next, you'll need to log in as user postgres in order to run initdb. Confession time: I've never gotten sudo to work, so I always su to the user I want to execute the command as, then exit when I'm done. So su me.
So, the postgres user has no password by default, which means it's impossible to su directly to postgres. The only way I've found to execute a command as this user is to su root, and then su postgres. If anyone has a better way, or if you can explain sudoers configuration to me, let me know in the comments.
Anyway, as postgres, execute this command:
The -D option tells initdb what home directory to use.
That's all there is to creating the cluster, but don't exit the postgres user yet.
Up next: authentication and roles
But there's one thing I don't like: It's a pain to set up. Well, maybe pain is an overstatement. But PostgreSQL does not just work out of the box; it takes a little effort to get it set up.
I recently set up PostgreSQL on a new machine running Fedora, and it took a little research to find all the necessary steps. I'm going to try to pull everything together here.
Part 1: Create a database cluster
A database cluster is the collection of databases to be managed by a database server. The initdb command sets up a cluster with two default databases you'll need to have: postgres, which is used by many third party apps, and template1, which will be the template for the databases you create.
The cluster must have a home directory. This can be located anywhere on the machine; popular choices, according to the PostgreSQL documentation notes that /usr/local/pgsql/data and /var/lib/pgsql/data. You may have to create the directory first, then chown it to user postgres.
Next, you'll need to log in as user postgres in order to run initdb. Confession time: I've never gotten sudo to work, so I always su to the user I want to execute the command as, then exit when I'm done. So su me.
So, the postgres user has no password by default, which means it's impossible to su directly to postgres. The only way I've found to execute a command as this user is to su root, and then su postgres. If anyone has a better way, or if you can explain sudoers configuration to me, let me know in the comments.
Anyway, as postgres, execute this command:
initdb -D /usr/local/pgsql/data
The -D option tells initdb what home directory to use.
That's all there is to creating the cluster, but don't exit the postgres user yet.
Up next: authentication and roles
Labels: Linux, PostgreSQL
posted by BruceA at
10:16 PM
0 Comments:
Post a Comment
Links to this post:
Create a Link
<< Home