From 27cb9c40d8e60f9eb584db38aa451532858b07a9 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Wed, 23 Jun 1999 06:15:37 +0000 Subject: [PATCH] Add backup/restore info to Admin Guide. Split management chapter from start-ag.sgml to manage-ag.sgml. --- doc/src/sgml/admin.sgml | 50 +++--- doc/src/sgml/manage-ag.sgml | 297 ++++++++++++++++++++++++++++++++++++ doc/src/sgml/postgres.sgml | 34 +++-- doc/src/sgml/start-ag.sgml | 251 ++++++------------------------ 4 files changed, 394 insertions(+), 238 deletions(-) create mode 100644 doc/src/sgml/manage-ag.sgml diff --git a/doc/src/sgml/admin.sgml b/doc/src/sgml/admin.sgml index e062504218..182d3658ad 100644 --- a/doc/src/sgml/admin.sgml +++ b/doc/src/sgml/admin.sgml @@ -6,6 +6,10 @@ Derived from postgres.sgml. - thomas 1998-10-27 $Log: admin.sgml,v $ +Revision 1.15 1999/06/03 04:21:47 thomas +Markup changes for v6.5 release. +Clean out duplicate stuff in odbc.sgml resulting from a faulty patch. + Revision 1.14 1999/05/26 17:30:27 thomas Add chapters on CVS access, MVCC, SQL theory to the docs. Add an appendix with more details on date/time attributes and handling. @@ -50,28 +54,29 @@ Bigger updates to the installation instructions (install and config). - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + ]> @@ -153,6 +158,7 @@ Your name here... &runtime; &security; &start-ag; + &manage-ag; &trouble; &recovery; ®ress; diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml new file mode 100644 index 0000000000..d74237498f --- /dev/null +++ b/doc/src/sgml/manage-ag.sgml @@ -0,0 +1,297 @@ + + Managing a Database + + + If the Postgres + postmaster is up and running we can create + some databases to experiment with. Here, we describe the + basic commands for managing a database. + + + + Creating a Database + + + Let's say you want to create a database named mydb. + You can do this with the following command: + + +% createdb dbname + + + Postgres allows you to create + any number of databases + at a given site and you automatically become the + database administrator of the database you just created. + Database names must have an alphabetic first + character and are limited to 16 characters in length. + Not every user has authorization to become a database + administrator. If Postgres + 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. + + + + + Accessing a Database + + + Once you have constructed a database, you can access it + by: + + + + + running the Postgres terminal monitor program + (psql) which allows you to interactively + enter, edit, and execute SQL commands. + + + + + + writing a C program using the libpq subroutine + library. This allows you to submit SQL commands + from C and get answers and status messages back to + your program. This interface is discussed further + in the PostgreSQL Programmer's Guide. + + + + + You might want to start up psql, + to try out the examples in this manual. It can be activated for the + dbname database by typing the command: + + +% psql dbname + + + You will be greeted with the following message: + + +Welcome to the Postgres interactive sql monitor: + + type \? for help on slash commands + type \q to quit + type \g or terminate with semicolon to execute query +You are currently connected to the database: dbname + +dbname=> + + + + + This prompt indicates that the terminal monitor is listening + to you and that you can type SQL queries into a + workspace maintained by the terminal monitor. + The psql program responds to escape + codes that begin + with the backslash character, "\". For example, you + can get help on the syntax of various + Postgres SQL commands by typing: + + +dbname=> \h + + + Once you have finished entering your queries into the + workspace, you can pass the contents of the workspace + to the Postgres server by typing: + + +dbname=> \g + + + This tells the server to process the query. If you + terminate your query with a semicolon, the backslash-g is not + necessary. psql will automatically + process semicolon terminated queries. + To read queries from a file, instead of + entering them interactively, type: + + +dbname=> \i filename + + + To get out of psql and return to UNIX, type + + +dbname=> \q + + + and psql will quit and return + you to your command shell. (For more escape codes, type + backslash-h at the monitor prompt.) + White space (i.e., spaces, tabs and newlines) may be + used freely in SQL queries. + Single-line comments are denoted by two dashes + (--). Everything after the dashes up to the end of the + line is ignored. Multiple-line comments, and comments within a line, + are denoted by /* ... */, a convention borrowed + from Ingres. + + + + + Destroying a Database + + + If you are the database administrator for the database + mydb, you can destroy it using the following UNIX command: + + +% destroydb dbname + + + This action physically removes all of the UNIX files + associated with the database and cannot be undone, so + this should only be done with a great deal of forethought. + + + + It is also possible to destroy a database from within an + SQL session by using + + +> drop database dbname + + + + + + Backup and Restore + + + + Every database should be backed up on a regular basis. Since + Postgres manages it's own files in the + file system, it is not advisable to rely on + system backups of your file system for your database backups; + there is no guarantee that the files will be in a usable, + consistant state after restoration. + + + + + Postgres provides two utilities to + backup your system: pg_dump to backup + individual databases and + pg_dumpall to backup your installation + in one step. + + + + An individual database can be backed up using the following + command: + + +% pg_dump dbname > dbname.pgdump + + + and can be restored using + + +cat dbname.pgdump | psql dbname + + + + + This technique can be used to move databases to new + locations, and to rename existing databases. + + + + Large Databases + + + Author + + Written by Hannu Krosing on + 1999-06-19. + + + + + Since Postgres allows tables larger + than the maximum file size on your system, it can be problematic + to dump the table to a file, since the resulting file will likely + be larger than the maximum size allowed by your system. + + + As pg_dump writes to stdout, + you can just use standard *nix tools + to work around this possible problem: + + + + + Use compressed dumps: + + +% pg_dump dbname | gzip > filename.dump.gz + + + reload with + + +% createdb dbname +% gunzip -c filename.dump.gz | psql dbname + + +or + + +% cat filename.dump.gz | gunzip | psql dbname + + + + + + + Use split: + + +% pg_dump dbname | split -b 1m - filename.dump. + + +reload with + + +% createdb dbname +% cat filename.dump.* | pgsql dbname + + + + + + + + Of course, the name of the file + (filename) and the + content of the pg_dump output need not + match the name of the database. Also, the restored database can + have an arbitrary new name, so this mechanism is also suitable + for renaming databases. + + + + + diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgml index 23c9e61040..f849ca8c59 100644 --- a/doc/src/sgml/postgres.sgml +++ b/doc/src/sgml/postgres.sgml @@ -6,6 +6,10 @@ Other subset docs should be copied and shrunk from here. thomas 1998-02-23 $Log: postgres.sgml,v $ +Revision 1.27 1999/06/03 04:21:49 thomas +Markup changes for v6.5 release. +Clean out duplicate stuff in odbc.sgml resulting from a faulty patch. + Revision 1.26 1999/06/01 17:26:18 thomas Make sure that only one intro is included in the integrated doc. Multiple intros cause trouble since they have some section elements @@ -132,19 +136,20 @@ Move SQL reference pages up into the User's Guide. %allfiles; - - - - - - - - - - - - - + + + + + + + + + + + + + + @@ -262,6 +267,7 @@ Your name here... + &intro; &syntax; &datatype; &oper; @@ -298,6 +304,7 @@ Your name here... &runtime; &security; &start-ag; + &manage-ag; &trouble; &recovery; ®ress; @@ -375,7 +382,6 @@ Your name here... Introduction for new users. - &intro; &sql; &arch; &start; diff --git a/doc/src/sgml/start-ag.sgml b/doc/src/sgml/start-ag.sgml index 2f35e49504..19ce761bfd 100644 --- a/doc/src/sgml/start-ag.sgml +++ b/doc/src/sgml/start-ag.sgml @@ -4,37 +4,38 @@ - - thomas 1998-02-24 --> - - Adding and Deleting Users - - - createuser enables specific users to access - Postgres. - destroyuser removes users and - prevents them from accessing Postgres. - Note that these - commands only affect users with respect to - Postgres; - they have no effect on users other privileges or status with regards - to the underlying - operating system. - - - - - Disk Management - - - Alternate Locations - - + + Adding and Deleting Users + + + createuser enables specific users to access + Postgres. + destroyuser removes users and + prevents them from accessing Postgres. + + + + These commands only affect users with respect to + Postgres; + they have no effect on a user's other privileges or status with regards + to the underlying operating system. + + + + + Disk Management + + + Alternate Locations + + It is possible to create a database in a location other than the default location for the installation. Remember that all database access actually occurs through the database backend, so that any location specified must be accessible by the backend. - + Alternate database locations are created and referenced by an environment variable which gives the absolute path to the intended storage location. This environment variable must have been defined before the backend was started @@ -44,45 +45,45 @@ to avoid confusion and conflict with other variables. - - - In previous versions of Postgres, + + + In previous versions of Postgres, it was also permissable to use an absolute path name to specify an alternate storage location. The environment variable style of specification is to be preferred since it allows the site administrator more flexibility in managing disk storage. If you prefer using absolute paths, you may do so by defining - "ALLOW_ABSOLUTE_DBPATHS" and recompiling Postgres + "ALLOW_ABSOLUTE_DBPATHS" and recompiling Postgres To do this, either add this line - + #define ALLOW_ABSOLUTE_DBPATHS 1 - + to the file src/include/config.h, or by specifying - + CFLAGS+= -DALLOW_ABSOLUTE_DBPATHS - + in your Makefile.custom. - - + + - + Remember that database creation is actually performed by the database backend. Therefore, any environment variable specifying an alternate location must have been defined before the backend was started. To define an alternate location PGDATA2 pointing to /home/postgres/data, first type - + % setenv PGDATA2 /home/postgres/data - + to define the environment variable to be used with subsequent commands. Usually, you will want to define this variable in the - Postgres superuser's + Postgres superuser's .profile or .cshrc @@ -93,187 +94,33 @@ overwriting other variables. - + To create a data storage area in PGDATA2, ensure that /home/postgres already exists and is writable by the postgres administrator. Then from the command line, type - + % setenv PGDATA2 /home/postgres/data % initlocation $PGDATA2 Creating Postgres database system directory /home/postgres/data Creating Postgres database system directory /home/postgres/data/base - + - - To test the new location, create a database test by typing + + To test the new location, create a database test by typing - + % createdb -D PGDATA2 test % destroydb test - + - - - - - Managing a Database - - - Now that Postgres is up and running we can create - some databases to experiment with. Here, we describe the - basic commands for managing a database. - - - - Creating a Database - - - Let's say you want to create a database named mydb. - You can do this with the following command: - - -% createdb mydb - - - Postgres allows you to create - any number of databases - at a given site and you automatically become the - database administrator of the database you just created. - Database names must have an alphabetic first - character and are limited to 16 characters in length. - Not every user has authorization to become a database - administrator. If Postgres - 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. - - - - - Accessing a Database - - - Once you have constructed a database, you can access it - by: - - - - - running the Postgres terminal monitor program - (psql) which allows you to interactively - enter, edit, and execute SQL commands. - - - - - writing a C program using the libpq subroutine - library. This allows you to submit SQL commands - from C and get answers and status messages back to - your program. This interface is discussed further - in the PostgreSQL Programmer's Guide. - - - - - You might want to start up psql, - to try out the examples in this manual. It can be activated for the mydb - database by typing the command: - - -% psql mydb - - - You will be greeted with the following message: - -Welcome to the Postgres interactive sql monitor: - - type \? for help on slash commands - type \q to quit - type \g or terminate with semicolon to execute query -You are currently connected to the database: mydb - -mydb=> - - - - - This prompt indicates that the terminal monitor is listening - to you and that you can type SQL queries into a - workspace maintained by the terminal monitor. - The psql program responds to escape - codes that begin - with the backslash character, "\". For example, you - can get help on the syntax of various - Postgres SQL commands by typing: - - -mydb=> \h - - - Once you have finished entering your queries into the - workspace, you can pass the contents of the workspace - to the Postgres server by typing: - - -mydb=> \g - - - This tells the server to process the query. If you - terminate your query with a semicolon, the backslash-g is not - necessary. psql will automatically - process semicolon terminated queries. - To read queries from a file, say myFile, instead of - entering them interactively, type: - - -mydb=> \i fileName - - - To get out of psql and return to UNIX, type - - -mydb=> \q - - - and psql will quit and return - you to your command - shell. (For more escape codes, type backslash-h at the monitor - prompt.) - White space (i.e., spaces, tabs and newlines) may be - used freely in SQL queries. - Single-line comments are denoted by two dashes - (--). Everything after the dashes up to the end of the - line is ignored. Multiple-line comments, and comments within a line, - are denoted by /* ... */, a convention borrowed - from Ingres. - - - - - Destroying a Database - - - If you are the database administrator for the database - mydb, you can destroy it using the following UNIX command: - - -% destroydb mydb - - - This action physically removes all of the UNIX files - associated with the database and cannot be undone, so - this should only be done with a great deal of forethought. - - - - + +