PostgreSQL のバックアップ(No.1)

バックアップ一覧
差分を表示
現在との差分を表示
ソースを表示
PostgreSQL へ行く。
- 1 (2008-03-23 (日) 21:36:55)
- 2 (2008-10-25 (土) 02:47:00)

PostgreSQL

PostgreSQL
- pg_hba.conf
- SQL
- VACUUM
- EXPLAIN
- ODBC

http://www.postgresql.org/

useradd postgres

./configure --enable-multibyte=EUC_JP --with-perl --with-java

./configure --enable-nls=ja --with-perl --with-openssl

http://www.gnu.org/software/gettext/

wget ftp://mirrors.kernel.org/gnu/gettext/gettext-0.17.tar.gz
tar xzvf gettext-0.17.tar.gz
cd gettext-0.17
./configure
make
make install

make all
make install

chown -R pgsql:pgsql /usr/local/pgsql

export PATH="$PATH":/usr/local/pgsql/bin
export POSTGRES_HOME=/usr/local/pgsql
export PGLIB=$POSTGRES_HOME/lib
export PGDATA=$POSTGRES_HOME/data
export MANPATH="$MANPATH":$POSTGRES_HOME/man
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH":"$PGLIB"

su - postgres
initdb --encoding=EUC_JP

initdb -D /usr/local/pgsql/data -E utf8

↑

pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/log/pg.log start

pg_ctl

-D datadir

-o options

-l filename

-s

-p path

postmaster
```
-B buffers
```
```
-i
```
```
-p port
```
```
-S
```
```
status
```

↑

#! /bin/sh
# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS
# This is an example of a start/stop script for SysV-style init, such
# as is used on Linux systems.  You should edit some of the variables
# and maybe the 'echo' commands.
#
# Proper init scripts on Linux systems normally require setting lock
# and pid files under /var/run as well as reacting to network
# settings, so you should treat this with care.
# Original author:  Ryan Kirkpatrick 
# $Header: /cvsroot/pgsql-server/contrib/start-scripts/linux,v 1.3 2001/07/30 14:52:42 momjian Exp $
## EDIT FROM HERE
# Installation prefix
prefix=/usr/local/pgsql
# Data directory
PGDATA="/usr/local/pgsql/data"
# Who to run pg_ctl as, should be "postgres".
PGUSER=postgres
# Where to keep a log file
PGLOG="$PGDATA/serverlog"
## STOP EDITING HERE
# Check for echo -n vs echo \c
if echo '\c' | grep -s c >/dev/null 2>&1 ; then
   ECHO_N="echo -n"
   ECHO_C=""
else
   ECHO_N="echo"
   ECHO_C='\c'
fi
# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
# What to use to start up the postmaster
DAEMON="$prefix/bin/pg_ctl"
set -e
# Only start if we can find pg_ctl.
test -f $DAEMON || exit 0
# Parse command line parameters.
case $1 in
 start)
       $ECHO_N "Starting PostgreSQL: "$ECHO_C
       su - $PGUSER -c "$DAEMON start -o '-i' -D '$PGDATA' -s -l $PGLOG"
       echo "ok"
       ;;
 stop)
       echo -n "Stopping PostgreSQL: "
       su - $PGUSER -c "$DAEMON stop -D '$PGDATA' -s -m fast"
       echo "ok"
       ;;
 restart)
       echo -n "Restarting PostgreSQL: "
       su - $PGUSER -c "$DAEMON restart -o '-i' -D '$PGDATA' -s -m fast -l $PGLOG"
       echo "ok"
       ;;
 status)
       su - $PGUSER -c "$DAEMON status -D '$PGDATA'"
       ;;
 *)
       # Print help
       echo "Usage: $0 {start|stop|restart|status}" 1>&2
       exit 1
       ;;
esac
exit 0

↑

pg_hba.conf

host   all all 192.168.1.0  255.255.255.0 trust

TYPE,DATABASE,USER,IP_ADDRES,IP-MASK,METHOD





METHOD

listen_addresses = '*'

↑

psql -l

pg_dump dbname > /backup/backupfile

psql dbname < /backup/backupfile

createuser

↑

SQL

select usename from pg_user;

CREATE USER user WITH PASSWORD 'password';

ALTER USER user WITH PASSWORD password';

DROP USER user

↑

VACUUM

vacuumdb test

↑

EXPLAIN

EXPLAIN sql;
EXPLAIN ANALYZE sql;

↑

ODBC

http://pgfoundry.org/projects/psqlodbc/
http://www.postgresql.org/ftp/odbc/versions/msi/
http://www.interwiz.koganei.tokyo.jp/software/PsqlODBC/index.html

↑

http://www.stackasterisk.jp/tech/dataBase/postgresql01_01.jsp
http://www.mediaweb.biz/database/db/psql_command1.html

↑