Creating database in postgresql ubuntu

Creating database in postgresql ubuntu

CREATE DATABASE creates a new PostgreSQL database.

To create a database, you must be a superuser or have the special CREATEDB privilege. See CREATE ROLE .

By default, the new database will be created by cloning the standard system database template1 . A different template can be specified by writing TEMPLATE name . In particular, by writing TEMPLATE template0 , you can create a pristine database (one where no user-defined objects exist and where the system objects have not been altered) containing only the standard objects predefined by your version of PostgreSQL . This is useful if you wish to avoid copying any installation-local objects that might have been added to template1 .

Parameters

The name of a database to create.

The role name of the user who will own the new database, or DEFAULT to use the default (namely, the user executing the command). To create a database owned by another role, you must be a direct or indirect member of that role, or be a superuser.

The name of the template from which to create the new database, or DEFAULT to use the default template ( template1 ).

Character set encoding to use in the new database. Specify a string constant (e.g., ‘SQL_ASCII’ ), or an integer encoding number, or DEFAULT to use the default encoding (namely, the encoding of the template database). The character sets supported by the PostgreSQL server are described in Section 24.3.1. See below for additional restrictions.

Strategy to be used in creating the new database. If the WAL_LOG strategy is used, the database will be copied block by block and each block will be separately written to the write-ahead log. This is the most efficient strategy in cases where the template database is small, and therefore it is the default. The older FILE_COPY strategy is also available. This strategy writes a small record to the write-ahead log for each tablespace used by the target database. Each such record represents copying an entire directory to a new location at the filesystem level. While this does reduce the write-ahead log volume substantially, especially if the template database is large, it also forces the system to perform a checkpoint both before and after the creation of the new database. In some situations, this may have a noticeable negative impact on overall system performance.

This is a shortcut for setting LC_COLLATE and LC_CTYPE at once.

The other locale settings lc_messages, lc_monetary, lc_numeric, and lc_time are not fixed per database and are not set by this command. If you want to make them the default for a specific database, you can use ALTER DATABASE . SET .

Collation order ( LC_COLLATE ) to use in the new database. This affects the sort order applied to strings, e.g., in queries with ORDER BY, as well as the order used in indexes on text columns. The default is to use the collation order of the template database. See below for additional restrictions.

Character classification ( LC_CTYPE ) to use in the new database. This affects the categorization of characters, e.g., lower, upper and digit. The default is to use the character classification of the template database. See below for additional restrictions.

Specifies the ICU locale ID if the ICU locale provider is used.

Specifies the provider to use for the default collation in this database. Possible values are: icu , libc . libc is the default. The available choices depend on the operating system and build options.

Specifies the collation version string to store with the database. Normally, this should be omitted, which will cause the version to be computed from the actual version of the database collation as provided by the operating system. This option is intended to be used by pg_upgrade for copying the version from an existing installation.

See also ALTER DATABASE for how to handle database collation version mismatches.

The name of the tablespace that will be associated with the new database, or DEFAULT to use the template database’s tablespace. This tablespace will be the default tablespace used for objects created in this database. See CREATE TABLESPACE for more information.

If false then no one can connect to this database. The default is true, allowing connections (except as restricted by other mechanisms, such as GRANT / REVOKE CONNECT ).

How many concurrent connections can be made to this database. -1 (the default) means no limit.

If true, then this database can be cloned by any user with CREATEDB privileges; if false (the default), then only superusers or the owner of the database can clone it.

The object identifier to be used for the new database. If this parameter is not specified, PostgreSQL will choose a suitable OID automatically. This parameter is primarily intended for internal use by pg_upgrade , and only pg_upgrade can specify a value less than 16384.

Optional parameters can be written in any order, not only the order illustrated above.

Читайте также:  Postgresql object large alter

Notes

CREATE DATABASE cannot be executed inside a transaction block.

Errors along the line of “ could not initialize database directory ” are most likely related to insufficient permissions on the data directory, a full disk, or other file system problems.

Use DROP DATABASE to remove a database.

The program createdb is a wrapper program around this command, provided for convenience.

Database-level configuration parameters (set via ALTER DATABASE ) and database-level permissions (set via GRANT ) are not copied from the template database.

Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose “ COPY DATABASE ” facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes. See Section 23.3 for more information.

The character set encoding specified for the new database must be compatible with the chosen locale settings ( LC_COLLATE and LC_CTYPE ). If the locale is C (or equivalently POSIX ), then all encodings are allowed, but for other locale settings there is only one encoding that will work properly. (On Windows, however, UTF-8 encoding can be used with any locale.) CREATE DATABASE will allow superusers to specify SQL_ASCII encoding regardless of the locale settings, but this choice is deprecated and may result in misbehavior of character-string functions if data that is not encoding-compatible with the locale is stored in the database.

The encoding and locale settings must match those of the template database, except when template0 is used as template. This is because other databases might contain data that does not match the specified encoding, or might contain indexes whose sort ordering is affected by LC_COLLATE and LC_CTYPE . Copying such data would result in a database that is corrupt according to the new settings. template0 , however, is known to not contain any data or indexes that would be affected.

There is currently no option to use a database locale with nondeterministic comparisons (see CREATE COLLATION for an explanation). If this is needed, then per-column collations would need to be used.

The CONNECTION LIMIT option is only enforced approximately; if two new sessions start at about the same time when just one connection “ slot ” remains for the database, it is possible that both will fail. Also, the limit is not enforced against superusers or background worker processes.

Examples

To create a new database:

To create a database sales owned by user salesapp with a default tablespace of salesspace :

To create a database music with a different locale:

In this example, the TEMPLATE template0 clause is required if the specified locale is different from the one in template1 . (If it is not, then specifying the locale explicitly is redundant.)

To create a database music2 with a different locale and a different character set encoding:

The specified locale and encoding settings must match, or an error will be reported.

Note that locale names are specific to the operating system, so that the above commands might not work in the same way everywhere.

Compatibility

There is no CREATE DATABASE statement in the SQL standard. Databases are equivalent to catalogs, whose creation is implementation-defined.

See Also

Prev Up Next
CREATE CONVERSION Home CREATE DOMAIN

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Copyright © 1996-2022 The PostgreSQL Global Development Group

Источник

Ubuntu Documentation

Introduction

PostgreSQL is a powerful object-relational database management system, provided under a flexible BSD-style license. [1] PostgreSQL contains many advanced features, is very fast and standards compliant.

PostgreSQL has bindings for many programming languages such as C, C++, Python, Java, PHP, Ruby. It can be used to power anything from simple web applications to massive databases with millions of records.

Client Installation

If you only wish to connect to an external PostgreSQL server, do not install the main PostgreSQL package, but install the PostgreSQL client package instead. To do this, use the following command

you then connect to the server with the following command

After you inserted the password you access PostgreSQL with line commands. You may for instance insert the following

You exit the connection with

Installation

To install the server locally use the command line and type:

This will install the latest version available in your Ubuntu release and the commonly used add-ons for it.

See «External Links» below for options for getting newer releases.

Installing PostGIS, procedural languages, client interfaces, etc

Additional packages contain procedural language runtimes, add-ons like PostGIS, language client interfaces like psycopg2 for Python, etc. You can get a listing with:

Administration

pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line:

You may also use the Synaptic package manager from the System>Administration menu to install these packages.

Basic Server Setup

To start off, we need to set the password of the PostgreSQL user (role) called «postgres»; we will not be able to access the server externally otherwise. As the local “postgres” Linux user, we are allowed to connect and manipulate the server using the psql command.

In a terminal, type:

this connects as a role with same name as the local user, i.e. «postgres», to the database called «postgres» (1st argument to psql).

Читайте также:  Python для системного администратора linux

Set a password for the «postgres» database role using the command:

and give your password when prompted. The password text will be hidden from the console for security purposes.

Type Control+D or \q to exit the posgreSQL prompt.

Create database

To create the first database, which we will call «mydb», simply type:

Install Server Instrumentation (for PgAdmin) for Postgresql 8.4 or 9.3

PgAdmin requires the installation of an add-on for full functionality. The «adminpack» addon, which it calls Server Instrumentation, is part of postgresql-contrib, so you must install that package if you haven’t already:

Then to activate the extension, for «»Postgresql 8.4″», run the adminpack.sql script, simply type:

For «Postgresql 9.3″+ install the adminpack «extension» in the «postgres» database:

Alternative Server Setup

If you don’t intend to connect to the database from other machines, this alternative setup may be simpler.

By default in Ubuntu, Postgresql is configured to use ‘ident sameuser’ authentication for any connections from the same machine. Check out the excellent Postgresql documentation for more information, but essentially this means that if your Ubuntu username is ‘foo’ and you add ‘foo’ as a Postgresql user then you can connect to the database without requiring a password.

Since the only user who can connect to a fresh install is the postgres user, here is how to create yourself a database account (which is in this case also a database superuser) with the same name as your login name and then create a password for the user:

Client programs, by default, connect to the local host using your Ubuntu login name and expect to find a database with that name too. So to make things REALLY easy, use your new superuser privileges granted above to create a database with the same name as your login name:

Connecting to your own database to try out some SQL should now be as easy as:

Creating additional database is just as easy, so for example, after running this:

You can go right ahead and tell Amarok to use postgresql to store its music catalog. The database name would be amarokdb, the username would be your own login name, and you don’t even need a password thanks to ‘ident sameuser’ so you can leave that blank.

Using pgAdmin III GUI

To get an idea of what PostgreSQL can do, you may start by firing up a graphical client. In a terminal type :

You will be presented with the pgAdmin III interface. Click on the «Add a connection to a server» button (top left). In the new dialog, enter the address 127.0.0.1 (Local host is default, so it can be left out.), a description of the server, the default database («mydb» in the example above), your username («postgres») and your password. One more step is required in order to allow pgAdmin III to connect to the server, and that is to edit pg_hba.conf file and change the authentication method from peer to md5 (will not work if you have not set the password):

and change the line

Now you should reload the server configuration changes and connect pgAdmin III to your PostgreSQL database server.

With this GUI you may start creating and managing databases, query the database, execute SQl etc.

Managing the Server

To learn more about managing PostgreSQL (but without the Ubuntu specifics) see the official PostgreSQL documentation

Managing users and rights

User management is discussed in detail in the client authentication chapter of the PostgreSQL documentation; the following is an introduction to get you started.

To manage users, you first have to edit /etc/postgresql/current/main/pg_hba.conf and modify the default configuration which is very locked down and secure. For example, if you want postgres to manage its own users (not linked with system users), you will add the following line:

Which means that on your local network (10.0.0.0/24 — replace with your own local network !), postgres users can connect through the network to the database providing a classical couple user / password.

Besides allowing a user to connect over the network to the to a database on the server, you must enable PostgreSQL to listen across different networks. To do that, open up /etc/postgresql/current/main/postgresql.conf in your favourite editor and alter the listen_addresses as below:

to listen on all network interfaces. See the docs for listen_addresses for other options.

To create a database with a user that have full rights on the database, use the following command:

The first command line creates the user with no database creation rights (-D) with no add user rights -A) and will prompt you for entering a password (-P). The second command line create the database ‘mydb with ‘myuser‘ as owner.

This little example will probably suit most of your needs. For more details, please refer to the corresponding man pages or the online documentation.

restarting the server

After configuring the networking / users you may need to reload the server, here is a suggested command to do so.

Some settings changes in postgresql.conf require a full restart, which will terminate active connections and abort uncommitted transactions:

Further reading

If you are not familiar with SQL you may want to look into this powerful language, although some simple uses of PostgreSQL may not require this knowledge (such as a simple Django project).

The PostgreSQL website contains a wealth of information on using this database. In particular, the tutorial is a useful starting point, but you can skip the installation step as you’ve already installed it using Ubuntu packages.

Читайте также:  Linux сброс настроек системы

Troubleshooting

fe_sendauth: no password supplied

Your pg_hba.conf specifies that md5 authentication is to be used for this connection based on the origin host, connection method and the requested username/database, but your application didn’t supply a password.

Change the authentication mode or set a password for the user you’re connecting to and then specify that password in your application’s connection settings.

FATAL: role «myusername» does not exist

By default PostgreSQL connects to the PostgreSQL user with the same name as the current unix user. You have not created a PostgreSQL user by that name in your database.

Create a suitable user, or specify a different username to connect with. In the command line tools the -U flag does this.

FATAL: database «myusername» does not exist

A user named «myusername» exists, but there’s no database of the same name.

By default PostgreSQL connects to the database with the same name as the user you’re connecting as, but it doesn’t auto-create the database if it doesn’t exist.

Create the database, or specify a different database to connect to.

FATAL: Peer authentication failed for user «myusername»

You are connecting to localhost via a unix socket. A user named «myusername» exists, but your current unix user is not the same as that username. PostgreSQL is set to use «peer» authentication on unix sockets for this user/db combo so it requires your unix and postgresql usernames to match.

Connect from the unix user that matches the desired PostgreSQL user — perhaps with sudo -u theusername psql — or change pg_hba.conf to use a different authentication mode like «md5» for this username.

could not connect to server: No such file or directory

An error like this (possibly with a different unix socket path, depending on your install):

can mean a number of things:

* The server isn’t running;

* The server has a different unix_socket_directories to the default in your client’s libpq, either due to different compiled-in defaults or a mismatched setting;

* The server is listening on a different «port». PostgreSQL emulates TCP/IP ports on unix sockets by using the port number as the suffix for the socket file, e.g. 5432.

Eliminate these in turn.

First make sure the server is running. On Ubuntu, ps -u postgres -f will show you any processes running as user postgres — you want to see multiple ones named postgres.

Now make sure the server is listening where your client thinks it is. To find out your PostgreSQL server’s socket directory:

or on older PostgreSQL versions, unix_socket_directory as the parameter changed name. To show the server’s port (which applies for both TCP/IP and unix sockets):

If you can’t even connect with psql under unix user postgres you can check the socket dir with lsof:

In this case the first line is the socket location. This server has socket directory /tmp with port 5432.

If your client is looking in a different socket directory, you’re probably trying to connect over unix sockets to the default socket path and/or to the default port, and the libpq your client application is linked to has a different compiled-in unix socket path and/or port than your running PostgreSQL. Most likely your LD_LIBRARY_PATH or /etc/ld.so.conf has a different libpq before the one that came with your version of PostgreSQL. This doesn’t generally matter much, you can just override the socket directory.

To specify an alternative socket directory and/port port to connect to, specify the socket dir as the host parameter in your connection options, e.g. to connect as user bob to the server listening in /tmp on port 5433:

or in connection-string form:

The same works with any client that uses libpq (all the PostgreSQL client tools, plus e.g. psycopg2, the Pg gem in Ruby/Rails, PHP’s postgres and PDO, Perl’s DBB::Pg, etc). It does NOT work with non-libpq clients like PgJDBC, py-postgresql, etc, but most of these don’t support unix sockets at all. See the client documentation for non-libpq based clients.

[1] You do not have to pay in order to use PostgreSQL for any application, such as commercial closed source software. See http://www.postgresql.org/about/licence/.

Official PostgreSQL downloads

The PostgreSQL project provides an official list of download locations, including an Ubuntu software repository, at its download page. In particular, Ubuntu users can get newer PostgreSQL versions than those packaged in their Ubuntu release using apt-get via apt.postgresql.org.

For support and services around PostgreSQL see the services and support page.

EnterpriseDB

The PostgreSQL Linux downloads page contains a section on «Graphical installer» built by EnterpriseDB. You download the installer, change its properties to allow to run it as command (it has .run extension), and run it from command prompt as in «sudo whateveritwas.run».

You end up with

  • configured DB server instance, which starts with your server
  • pgAdmin III UI client application

Note that the installed software will not be integrated with Ubuntu software center. As a result, you will not receive any security updates from Ubuntu. However, the installed version will closely match the latest Ubuntu version.

Turnkey Linux

An Ubuntu-based PostgreSQL appliance is one of the easiest ways to get up and running with PostgreSQL on Ubuntu. It’s part of a family of pre-integrated TurnKey Linux Software Appliances based on Ubuntu 10.04.1 (Lucid LTS).

PostgreSQL (последним исправлял пользователь mikini 2015-08-28 13:16:48)

The material on this wiki is available under a free license, see Copyright / License for details
You can contribute to this wiki, see Wiki Guide for details

Источник

Поделиться с друзьями
КомпСовет
Adblock
detector