Creating additional geodatabases in PostgreSQL

You can create more geodatabases on your enterprise geodatabase (EGDB) instance using the Create ArcSDE Geodatabase wizard.

Some common reasons you would create additional geodatabases include the following:

• You want to customize your geodatabase.

  The default geodatabase (sdegdb) cannot be renamed, nor can it be moved to a different tablespace. If you want a geodatabase with a different name or want it to use a tablespace other than the sdegdb tablespace in the pgdata folder on the D drive, you must create another geodatabase.

• You want to group your data by geodatabase.

  For example, if you have multiple departments that maintain their own discrete data, you might create separate geodatabases for each group.

Steps:

1. Make a remote desktop connection to your EGDB instance server.
2. Open the Create ArcSDE Geodatabase wizard.

   Start > All Programs > ArcGIS > ArcSDE > Create ArcSDE Geodatabase

3. Type a name for the new database in the Geodatabase name text box.
   Note:

   The name of the database cannot contain special characters, cannot be longer than 31 characters, and should be lower case.

4. Type the postgres user's password in the PostgreSQL administrator password text box.
5. Type the sde user's password in the ArcSDE administrator (sde) password text box.
Tip:

If you have not already reset the passwords, see the default passwords on Enterprise geodatabase on Amazon EC2.

6. In the Default tablespace name text box, type a name for the tablespace that will be used as the default tablespace for the database.

   Notice that the name of the tablespace folder changes in the default value for Tablespace folder to match what you type in the Default tablespace name text box. The folder and tablespace do not have to match, but it is recommended that you use the same name. Both names should be lower-case.

7. If you want to designate a different location for the tablespace folder—for example, if you have multiple EBS drives on your EGDB instance server—specify the path and tablespace folder name in the Tablespace folder text box. You can either type the path and name or click Browse to navigate to an existing folder.
   Note:

   The folder in which the tablespace is created must be empty. If it is not, an error is returned to you.

8. Click Create to create the geodatabase.

   If the information you provided was correct, you receive a message indicating the geodatabase was successfully created. If you receive an error message, be sure the information you provided was correct (for example, you used the correct passwords, the specified tablespace folder is empty, and the database name is valid).

   Tip:

   Check the PostgreSQL log file at pgdata\pg_log for extended error messages.

9. Click OK to close the returned message.
10. Click the Close button on the dialog box to close it and click Yes when prompted to confirm you want to exit the wizard.

You now have a new geodatabase. You must add user schemas to the database for all login roles that will create data in the geodatabase.

11. Start pgAdmin III and connect to the PostgreSQL database cluster.
12. Expand the Databases node in the Object browser window.
13. Connect to your new database.
14. Right-click Schemas and click New Schema.
15. Type a name for the schema.
    Note:

    The name of the schema must be the same as the login role name.

16. Choose the login role from the Owner list.
17. Any users who need access to data in this schema need usage permissions on the schema.
    • If you grant usage to the public role, you can do that by clicking the Privileges tab of the New Schema dialog box, choosing USAGE, then clicking Add/Change. Click OK to create the schema.
    • If you want to grant usage privileges to specific group or login roles, click OK to create the schema, open the SQL editor, then execute SQL statements to grant permissions to specific roles. The syntax for this is as follows:

      GRANT usage 
      ON SCHEMA <schema_name>
      TO <role>;

      Repeat this for each group or login role that needs to access the data in this schema.