Changed the postgres data dir
to /home/postgres
from /var/lib/postgres
Edit /home/postgres/.profile
Edit /etc/passwd for postgres home dir
Edit /usr/lib/postgres/bin/postgresql-startup
Help with Command
Remeber to ask for help '\?'
Welcome to psql 7.4.19, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
freeside=> \?
General
\c[onnect] [DBNAME|- [USER]]
connect to new database (currently "freeside")
\cd [DIR] change the current working directory
\copyright show PostgreSQL usage and distribution terms
\encoding [ENCODING]
show or set client encoding
\h [NAME] help on syntax of SQL commands, * for all commands
\q quit psql
\set [NAME [VALUE]]
set internal variable, or list all if no parameters
\timing toggle timing of commands (currently off)
\unset NAME unset (delete) internal variable
\! [COMMAND] execute command in shell or start interactive shell
Query Buffer
\e [FILE] edit the query buffer (or file) with external editor
\g [FILE] send query buffer to server (and results to file or |pipe)
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w [FILE] write query buffer to file
Input/Output
\echo [STRING] write string to standard output
\i FILE execute commands from file
\o [FILE] send all query results to file or |pipe
\qecho [STRING]
write string to query output stream (see \o)
Informational
\d [NAME] describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
list tables/indexes/sequences/views/system tables
\da [PATTERN] list aggregate functions
\dc [PATTERN] list conversions
\dC list casts
\dd [PATTERN] show comment for object
\dD [PATTERN] list domains
\df [PATTERN] list functions (add "+" for more detail)
\dn [PATTERN] list schemas
\do [NAME] list operators
\dl list large objects, same as \lo_list
\dp [PATTERN] list table access privileges
\dT [PATTERN] list data types (add "+" for more detail)
\du [PATTERN] list users
\l list all databases (add "+" for more detail)
\z [PATTERN] list table access privileges (same as \dp)
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w [FILE] write query buffer to file
Input/Output
\echo [STRING] write string to standard output
\i FILE execute commands from file
\o [FILE] send all query results to file or |pipe
\qecho [STRING]
write string to query output stream (see \o)
Informational
\d [NAME] describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
list tables/indexes/sequences/views/system tables
\da [PATTERN] list aggregate functions
\dc [PATTERN] list conversions
\dC list casts
\dd [PATTERN] show comment for object
\dD [PATTERN] list domains
\df [PATTERN] list functions (add "+" for more detail)
\dn [PATTERN] list schemas
\do [NAME] list operators
\dl list large objects, same as \lo_list
\dp [PATTERN] list table access privileges
\dT [PATTERN] list data types (add "+" for more detail)
\du [PATTERN] list users
\l list all databases (add "+" for more detail)
\z [PATTERN] list table access privileges (same as \dp)
Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset NAME [VALUE]
set table output option
(NAME := {format|border|expanded|fieldsep|footer|null|
recordsep|tuples_only|title|tableattr|pager})
\t show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x toggle expanded output (currently off)
Copy, Large Object
\copy ... perform SQL COPY with data stream to the client host
\lo_export
\lo_import
\lo_list
\lo_unlink large object operations
How to get the nextval from a sequence on the psql command lines
select nextval( 'sequence_name');
How to create a table
create table
CREATE TABLE info (
iid INT2 PRIMARY KEY,
nid INT2 NOT NULL,
info TEXT,
creation DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
Working with users
as the user postgres, can reset/set the password for users.
su - postgres
# This will prompt for a user password
createuser -d -P sql-ledger
#createuser adbuser
#pgsql template1
#alter user adbuser password 'foubar1';
#\q
createdb -U adbuser the_db
exit
To List users,
select * from pg_user;
or
\du
To set the password
alter user freeside password 'InsertPasswordHere!';
Database Backup.
http://www.postgresql.org/docs/7/static/manage-ag17965.htm
pg_dumpall -h localhost > db_backup_20050206.pgsql
used localhost for auth reason.
pg_dump -c -C -f outputfile.psql database
Give postgres kill -HUP it will reread the config files.
To reload from the backup file, (NOTE: This will to a fresh load, delete all and then load the backup. Use at your own risk!)
dropdb -U dbuser thedatabase
createdb -U dbuser thedatabase
cat thedatabase_dbdump_20070601.text | psql -U dbuser thedatabase
Data Types Ver 7.4.7
List of data types
Schema | Name | Description
------------+-----------------------------+-------------------------------------------------------------------
pg_catalog | abstime | absolute, limited-range date and time (Unix system time)
pg_catalog | aclitem | access control list
pg_catalog | "any" |
pg_catalog | anyarray |
pg_catalog | anyelement |
pg_catalog | bigint | ~18 digit integer, 8-byte storage
pg_catalog | bit | fixed-length bit string
pg_catalog | bit varying | variable-length bit string
pg_catalog | boolean | boolean, 'true'/'false'
pg_catalog | box | geometric box '(lower left,upper right)'
pg_catalog | bytea | variable-length string, binary values escaped
pg_catalog | "char" | single character
pg_catalog | character | char(length), blank-padded string, fixed storage length
pg_catalog | character varying | varchar(length), non-blank-padded string, variable storage length
pg_catalog | cid | command identifier type, sequence in transaction id
pg_catalog | cidr | network IP address/netmask, network address
pg_catalog | circle | geometric circle '(center,radius)'
pg_catalog | cstring |
pg_catalog | date | ANSI SQL date
pg_catalog | double precision | double-precision floating point number, 8-byte storage
pg_catalog | inet | IP address/netmask, host address, netmask optional
pg_catalog | int2vector | array of 32 int2 integers, used in system tables
pg_catalog | integer | -2 billion to 2 billion integer, 4-byte storage
pg_catalog | internal |
pg_catalog | interval | @ <number> <units>, time interval
pg_catalog | language_handler |
pg_catalog | line | geometric line (not implemented)'
pg_catalog | lseg | geometric line segment '(pt1,pt2)'
pg_catalog | macaddr | XX:XX:XX:XX:XX:XX, MAC address
pg_catalog | money | monetary amounts, $d,ddd.cc
pg_catalog | name | 63-character type for storing system identifiers
pg_catalog | numeric | numeric(precision, decimal), arbitrary precision number
pg_catalog | oid | object identifier(oid), maximum 4 billion
pg_catalog | oid | object identifier(oid), maximum 4 billion
pg_catalog | oidvector | array of 32 oids, used in system tables
pg_catalog | opaque |
pg_catalog | "path" | geometric path '(pt1,...)'
pg_catalog | point | geometric point '(x, y)'
pg_catalog | polygon | geometric polygon '(pt1,...)'
pg_catalog | real | single-precision floating point number, 4-byte storage
pg_catalog | record |
pg_catalog | refcursor | reference cursor (portal name)
pg_catalog | regclass | registered class
pg_catalog | regoper | registered operator
pg_catalog | regoperator | registered operator (with args)
pg_catalog | regproc | registered procedure
pg_catalog | regprocedure | registered procedure (with args)
pg_catalog | regprocedure | registered procedure (with args)
pg_catalog | regtype | registered type
pg_catalog | reltime | relative, limited-range time interval (Unix delta time)
pg_catalog | "SET" | set of tuples
pg_catalog | smallint | -32 thousand to 32 thousand, 2-byte storage
pg_catalog | smgr | storage manager
pg_catalog | text | variable-length string, no limit specified
pg_catalog | tid | (Block, offset), physical location of tuple
pg_catalog | timestamp without time zone | date and time
pg_catalog | timestamp with time zone | date and time with time zone
pg_catalog | time without time zone | hh:mm:ss, ANSI SQL time
pg_catalog | time with time zone | hh:mm:ss, ANSI SQL time
pg_catalog | tinterval | (abstime,abstime), time interval
pg_catalog | "trigger" |
pg_catalog | "unknown" |
pg_catalog | void |
pg_catalog | xid | transaction id
(62 rows)
You are not logged in. Log In 