Creating a Database

1.3. Creating a Database

 

The first test to see whether you can access the database server is to try to create a database. A running PostgreSQL server can manage many databases. Typically, a separate database is used for each project or for each user.

 

Possibly, your site administrator has already created a database for your use. In that case you can omit this step and skip ahead to the next section.

 

To create a new database, in this example named mydb, you use the following command:

$ createdb mydb

If this produces no response then this step was successful and you can skip over the remainder of this section.

 

If you see a message similar to:

createdb: command not found

then PostgreSQL was not installed properly. Either it was not installed at all or your shell's search path was not set to include it. Try calling the command with an absolute path instead:

$ /usr/local/pgsql/bin/createdb mydb

 

The path at your site might be different. Contact your site administrator or check the installation instructions to correct the situation.

 

Another response could be this:

createdb: could not connect to database postgres: could not connect to server: No such file or directory

Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

This means that the server was not started, or it was not started where createdb expected it. Again, check the installation instructions or consult the administrator.

 

Another response could be this:

createdb: could not connect to database postgres: FATAL: role "joe" does not exist

where your own login name is mentioned. This will happen if the administrator has not created a PostgreSQL user account for you. (PostgreSQL user accounts are distinct from operating system user accounts.) If you are the administrator, see Chapter 21 for help creating accounts. You will need to become the operating system user under which PostgreSQL was installed (usually postgres) to create the first user account. It could also be that you were assigned a PostgreSQL user name that is different from your operating system user name; in that case you need to use the -U switch or set the PGUSER environment variable to specify your PostgreSQL user name.

 

If you have a user account but it does not have the privileges required to create a database, you will see the following:

createdb: database creation failed: ERROR: permission denied to create database

Not every user has authorization to create new databases. If PostgreSQL refuses to create databases for you then the site administrator needs to grant you permission to create databases. Consult your site administrator if this occurs. If you installed PostgreSQL yourself then you should log in for the purposes of this tutorial under the user account that you started the server as. 

 

You can also create databases with other names. PostgreSQL allows you to create any number of databases at a given site. Database names must have an alphabetic first character and are limited to 63 bytes in length. A convenient choice is to create a database with the same name as your current user name. Many tools assume that database name as the default, so it can save you some typing. To create that database, simply type:

$ createdb

If you do not want to use your database anymore you can remove it. For example, if you are the owner (creator) of the database mydb, you can destroy it using the following command:

$ dropdb mydb

(For this command, the database name does not default to the user account name. You always need to specify it.) This action physically removes all files associated with the database and cannot be undone, so this should only be done with a great deal of forethought.

 

More about createdb and dropdb can be found in createdb and dropdb respectively.

 

1.3 创建数据库

 

可以尝试创建一个数据库以验证你是否可以访问数据库服务端。PostgreSQL服务器可以管理多个数据库。一般情况下，每个项目或者每个用户使用自己单独的数据库。

 

或许，你的管理员已经为你专门创建了一个数据库。那么，你就可以跳过这个章节，直接开始下一章节的学习了。

 

使用以下命令创建一个名为mydb的数据库:

$ createdb mydb

如果此命令未返回其他提示信息，那么说明此命令成功执行了。那么本章节剩下的内容就可以跳过了。

 

但如果返回了错误信息：

createdb: command not found

那么PostgreSQL就是没有正确的安装或者配置。或许你的操作系统上根本就没有安装PostgreSQL，又或许没有正确的是指$PATH环境变量。尝试使用绝对路径调用此命令（以下为示例，请根据具体情况修改命令所在路径）：

$ /usr/local/pgsql/bin/createdb mydb

 

还有可能返回如下信息：

createdb: could not connect to database postgres: could not connect to server: No such file or directory

Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

这个反馈信息说明PostgreSQL服务没有启动，或者说，它没有在createdb期望的地方启动。那么，请通过查阅安装说明或者咨询管理员来处理这个问题。

 

还有可能返回如下信息：

createdb: could not connect to database postgres: FATAL: role "joe" does not exist

提示中，joe是你的用户名。如果管理员还没有为你创建PostgreSQL数据库账户名，那么会出现这个报错信息。如果恰巧你是管理员，那么请参考第21章，为自己创建数据库用户。你需要使用安装PostgreSQL的用户（一般是postgres）登录以创建数据库用户。也有可能分配给你的数据库用户名与你的操作系统用户名不同，那么就需要使用-U选项或者设置PGUSER变量来明确指定分配给你的数据库用户名。

 

如果你已经有了一个数据库用户，但是却没有权限创建数据库，那么会得到以下信息：

createdb: database creation failed: ERROR: permission denied to create database

不是所有的用户都有权限创建数据库。如果你的账户没有权限创建数据库，那么需要协调管理员给你赋予相应的权限。如果PostgreSQL是你自己安装的，那么可以直接使用启动PostgreSQL服务的用户进行本文档的一些实践。

 

当然，也可以创建其他名称的数据库。PostgreSQL对数据库的数量没有限制。数据库名必须以字母开头，在63字节之内。一个较为便捷的方式是，创建一个名称与你的账号名相同的数据库。许多工具会创建默认名称的数据库，这样可以少敲一些字。比如创建数据库的时候，可以只是简单的输入：

$ createdb

 

那当你不想要你创建的数据库时，可以将数据库删掉。例如，如果你是mydb的所有者，那么可以使用以下命令删除该数据库：

$ dropdb mydb

这个命令不会默认为数据库用户名为操作系统用户名，每次使用的时候，均需指定数据库名称。谨记：这个操作会删除掉所有该数据库相关的文件，且不可回滚。

 

关于createdb和dropdb的更多信息，请参考 createdb和 dropdb。