A Greenplum Database system can have one or more databases. This is different from some other database management systems (such as Oracle) where the database instance is the database. Although you can create many databases in a Greenplum system, client programs can connect and access one database at a time — you cannot cross-query between databases. About Template Databases Every new database you create is based on a template. A default database called template1 exists in every newly initialized Greenplum Database system. You can use this database to connect to Greenplum Database for the first time. This is the template used to create other databases by default if you do not explicitly declare a template when creating a new database. You do not want to create any objects in this database unless you want those objects to also be in every other database you create afterwards. In addition to template1, every newly created Greenplum system has two other database templates, template0 and postgres, which are used internally by the system and should not be dropped or modified. The template0 database template can be used to create a completely clean database containing only the standard objects predefined by Greenplum Database at initialization. This is useful if you wish to avoid copying any objects that may have been added to template1. Creating a Database The CREATE DATABASE command creates a new database. For example: => CREATE DATABASE new_dbname; In order to create a database, you must have privileges to create a database or be a Greenplum superuser. If you do not have the correct privileges, then you will not be able to create a database. Contact your Greenplum administrator to either give you the necessary privilege or to create a database for you. There is also a client program called createdb that you can use to create a database. For example, running the following command in a command line terminal will make a connection to Greenplum Database using the provided host name and port and create the database named mydatabase: $ createdb -h masterhost -p 5432 mydatabase Cloning a Database By default, a new database is created by cloning the standard system database template, template1. Any database can be used as a template when creating a new database, thereby providing the capability to ‘clone’ or copy an existing database and all of the objects and data within that database. For example: => CREATE DATABASE new_dbname TEMPLATE old_dbname; Viewing the List of Databases If you are working in the psql client program, you can use the \l meta-command to show the list of databases and templates in your Greenplum Database system. If using another client program, you can query the list of databases from the pg_database system catalog table (you must be a superuser). For example: => SELECT datname from pg_database; Altering a Database The ALTER DATABASE command is used to change certain attributes about a database, such its owner, name or default configuration attributes. You must be either the owner of the database or a superuser to alter it. Here is an example of altering a database to set its default schema search path (the search_path configuration parameter): => ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog; Dropping a Database The DROP DATABASE command can be used to drop (or delete) a database. It removes the system catalog entries for the database and also deletes the database directory on disk containing the data. You must be the database owner or a superuser to drop a database, and you cannot drop a database while you or anyone else is connected to it. Connect into template1 (or another database) before dropping a database. For example: => \c template1 => DROP DATABASE mydatabase; There is also a client program called dropdb that you can use to drop a database. For example, running the following command in a command line terminal will make a connection to Greenplum Database using the provided host name and port and drop the database named mydatabase: $ dropdb -h masterhost -p 5432 mydatabase |
|
|