[postgres@myLinux002 lib]$ pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
server starting
[postgres@myLinux002 lib]$ LOG: database system was shut down at 2014-11-15 08:12:53 UTC
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
[postgres@myLinux002 lib]$
postgres=# \dnp
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
postgres=# \dnp+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(1 row)
postgres=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
postgres=# create schemas sa;
ERROR: syntax error at or near "schemas"
LINE 1: create schemas sa;
^
postgres=# create schema sa;
CREATE SCHEMA
postgres=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
sa | postgres
(2 rows)
postgres=#
postgres=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
sa | postgres
(2 rows)
postgres=# \dnp
List of schemas
Name | Owner
--------+----------
public | postgres
sa | postgres
(2 rows)
postgres=# \dnp+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
sa | postgres | |
(2 rows)
b1=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | a1 | table | postgres
public | bb | table | test
public | cities | table | postgres
public | f | table | postgres
public | foo | table | postgres
public | mytable | table | postgres
public | on_hand | table | postgres
public | p | table | postgres
public | sal_emp | table | postgres
public | t1 | table | postgres
public | test | table | postgres
public | test1 | table | postgres
public | testdate | table | postgres
public | testtable | table | postgres
public | testtrue | table | postgres
public | weather | table | postgres
(16 rows)
db1=# \dts
List of relations
Schema | Name | Type | Owner
--------+-----------+----------+----------
public | a1 | table | postgres
public | bb | table | test
public | cities | table | postgres
public | f | table | postgres
public | foo | table | postgres
public | mytable | table | postgres
public | on_hand | table | postgres
public | p | table | postgres
public | sal_emp | table | postgres
public | t1 | table | postgres
public | t1_id_seq | sequence | postgres
public | test | table | postgres
public | test1 | table | postgres
public | testdate | table | postgres
public | testtable | table | postgres
public | testtrue | table | postgres
public | weather | table | postgres
(17 rows)
db1=# \dtS
List of relations
Schema | Name | Type | Owner
------------+-------------------------+-------+----------
pg_catalog | pg_aggregate | table | postgres
pg_catalog | pg_am | table | postgres
pg_catalog | pg_amop | table | postgres
pg_catalog | pg_amproc | table | postgres
pg_catalog | pg_attrdef | table | postgres
pg_catalog | pg_attribute | table | postgres
pg_catalog | pg_auth_members | table | postgres
pg_catalog | pg_authid | table | postgres
pg_catalog | pg_cast | table | postgres
pg_catalog | pg_class | table | postgres
pg_catalog | pg_collation | table | postgres
pg_catalog | pg_constraint | table | postgres
pg_catalog | pg_conversion | table | postgres
pg_catalog | pg_database | table | postgres
pg_catalog | pg_db_role_setting | table | postgres
pg_catalog | pg_default_acl | table | postgres
pg_catalog | pg_depend | table | postgres
pg_catalog | pg_description | table | postgres
pg_catalog | pg_enum | table | postgres
pg_catalog | pg_event_trigger | table | postgres
pg_catalog | pg_extension | table | postgres
pg_catalog | pg_foreign_data_wrapper | table | postgres
pg_catalog | pg_foreign_server | table | postgres
pg_catalog | pg_foreign_table | table | postgres
pg_catalog | pg_index | table | postgres
pg_catalog | pg_inherits | table | postgres
pg_catalog | pg_language | table | postgres
pg_catalog | pg_largeobject | table | postgres
pg_catalog | pg_largeobject_metadata | table | postgres
pg_catalog | pg_namespace | table | postgres
pg_catalog | pg_opclass | table | postgres
pg_catalog | pg_operator | table | postgres
pg_catalog | pg_opfamily | table | postgres
pg_catalog | pg_pltemplate | table | postgres
pg_catalog | pg_proc | table | postgres
pg_catalog | pg_range | table | postgres
pg_catalog | pg_rewrite | table | postgres
pg_catalog | pg_seclabel | table | postgres
pg_catalog | pg_shdepend | table | postgres
pg_catalog | pg_shdescription | table | postgres
pg_catalog | pg_shseclabel | table | postgres
pg_catalog | pg_statistic | table | postgres
pg_catalog | pg_tablespace | table | postgres
pg_catalog | pg_trigger | table | postgres
pg_catalog | pg_ts_config | table | postgres
pg_catalog | pg_ts_config_map | table | postgres
pg_catalog | pg_ts_dict | table | postgres
pg_catalog | pg_ts_parser | table | postgres
pg_catalog | pg_ts_template | table | postgres
pg_catalog | pg_type | table | postgres
pg_catalog | pg_user_mapping | table | postgres
public | a1 | table | postgres
public | bb | table | test
public | cities | table | postgres
public | f | table | postgres
public | foo | table | postgres
public | mytable | table | postgres
public | on_hand | table | postgres
public | p | table | postgres
public | sal_emp | table | postgres
public | t1 | table | postgres
public | test | table | postgres
public | test1 | table | postgres
public | testdate | table | postgres
public | testtable | table | postgres
public | testtrue | table | postgres
public | weather | table | postgres
(67 rows)
db1=# \dtS+
List of relations
Schema | Name | Type | Owner | Size | Description
------------+-------------------------+-------+----------+------------+-------------
pg_catalog | pg_aggregate | table | postgres | 40 kB |
pg_catalog | pg_am | table | postgres | 40 kB |
pg_catalog | pg_amop | table | postgres | 64 kB |
pg_catalog | pg_amproc | table | postgres | 56 kB |
pg_catalog | pg_attrdef | table | postgres | 16 kB |
pg_catalog | pg_attribute | table | postgres | 408 kB |
pg_catalog | pg_auth_members | table | postgres | 40 kB |
pg_catalog | pg_authid | table | postgres | 40 kB |
pg_catalog | pg_cast | table | postgres | 48 kB |
pg_catalog | pg_class | table | postgres | 104 kB |
pg_catalog | pg_collation | table | postgres | 248 kB |
pg_catalog | pg_constraint | table | postgres | 48 kB |
pg_catalog | pg_conversion | table | postgres | 56 kB |
pg_catalog | pg_database | table | postgres | 40 kB |
pg_catalog | pg_db_role_setting | table | postgres | 8192 bytes |
pg_catalog | pg_default_acl | table | postgres | 0 bytes |
pg_catalog | pg_depend | table | postgres | 416 kB |
pg_catalog | pg_description | table | postgres | 288 kB |
pg_catalog | pg_enum | table | postgres | 0 bytes |
pg_catalog | pg_event_trigger | table | postgres | 0 bytes |
pg_catalog | pg_extension | table | postgres | 40 kB |
pg_catalog | pg_foreign_data_wrapper | table | postgres | 0 bytes |
pg_catalog | pg_foreign_server | table | postgres | 0 bytes |
pg_catalog | pg_foreign_table | table | postgres | 0 bytes |
pg_catalog | pg_index | table | postgres | 56 kB |
pg_catalog | pg_inherits | table | postgres | 0 bytes |
pg_catalog | pg_language | table | postgres | 40 kB |
pg_catalog | pg_largeobject | table | postgres | 0 bytes |
pg_catalog | pg_largeobject_metadata | table | postgres | 0 bytes |
pg_catalog | pg_namespace | table | postgres | 40 kB |
pg_catalog | pg_opclass | table | postgres | 48 kB |
pg_catalog | pg_operator | table | postgres | 144 kB |
pg_catalog | pg_opfamily | table | postgres | 48 kB |
pg_catalog | pg_pltemplate | table | postgres | 40 kB |
pg_catalog | pg_proc | table | postgres | 544 kB |
pg_catalog | pg_range | table | postgres | 40 kB |
pg_catalog | pg_rewrite | table | postgres | 512 kB |
pg_catalog | pg_seclabel | table | postgres | 8192 bytes |
pg_catalog | pg_shdepend | table | postgres | 40 kB |
pg_catalog | pg_shdescription | table | postgres | 48 kB |
pg_catalog | pg_shseclabel | table | postgres | 0 bytes |
pg_catalog | pg_statistic | table | postgres | 256 kB |
pg_catalog | pg_tablespace | table | postgres | 40 kB |
pg_catalog | pg_trigger | table | postgres | 48 kB |
pg_catalog | pg_ts_config | table | postgres | 40 kB |
pg_catalog | pg_ts_config_map | table | postgres | 48 kB |
pg_catalog | pg_ts_dict | table | postgres | 40 kB |
pg_catalog | pg_ts_parser | table | postgres | 40 kB |
pg_catalog | pg_ts_template | table | postgres | 40 kB |
pg_catalog | pg_type | table | postgres | 104 kB |
pg_catalog | pg_user_mapping | table | postgres | 0 bytes |
public | a1 | table | postgres | 40 kB |
public | bb | table | test | 744 kB |
public | cities | table | postgres | 40 kB |
public | f | table | postgres | 872 kB |
public | foo | table | postgres | 0 bytes |
public | mytable | table | postgres | 8192 bytes |
public | on_hand | table | postgres | 8192 bytes |
public | p | table | postgres | 792 kB |
public | sal_emp | table | postgres | 16 kB |
public | t1 | table | postgres | 16 kB |
public | test | table | postgres | 792 kB |
public | test1 | table | postgres | 792 kB |
public | testdate | table | postgres | 8192 bytes |
public | testtable | table | postgres | 8192 bytes |
public | testtrue | table | postgres | 16 kB |
public | weather | table | postgres | 40 kB |
(67 rows)
db1=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------+-------+----------+------------+-------------
public | a1 | table | postgres | 40 kB |
public | bb | table | test | 744 kB |
public | cities | table | postgres | 40 kB |
public | f | table | postgres | 872 kB |
public | foo | table | postgres | 0 bytes |
public | mytable | table | postgres | 8192 bytes |
public | on_hand | table | postgres | 8192 bytes |
public | p | table | postgres | 792 kB |
public | sal_emp | table | postgres | 16 kB |
public | t1 | table | postgres | 16 kB |
public | test | table | postgres | 792 kB |
public | test1 | table | postgres | 792 kB |
public | testdate | table | postgres | 8192 bytes |
public | testtable | table | postgres | 8192 bytes |
public | testtrue | table | postgres | 16 kB |
public | weather | table | postgres | 40 kB |
(16 rows)
db1=# \dtS+
List of relations
Schema | Name | Type | Owner | Size | Description
------------+-------------------------+-------+----------+------------+-------------
pg_catalog | pg_aggregate | table | postgres | 40 kB |
pg_catalog | pg_am | table | postgres | 40 kB |
pg_catalog | pg_amop | table | postgres | 64 kB |
pg_catalog | pg_amproc | table | postgres | 56 kB |
pg_catalog | pg_attrdef | table | postgres | 16 kB |
pg_catalog | pg_attribute | table | postgres | 408 kB |
pg_catalog | pg_auth_members | table | postgres | 40 kB |
pg_catalog | pg_authid | table | postgres | 40 kB |
pg_catalog | pg_cast | table | postgres | 48 kB |
pg_catalog | pg_class | table | postgres | 104 kB |
pg_catalog | pg_collation | table | postgres | 248 kB |
pg_catalog | pg_constraint | table | postgres | 48 kB |
pg_catalog | pg_conversion | table | postgres | 56 kB |
pg_catalog | pg_database | table | postgres | 40 kB |
pg_catalog | pg_db_role_setting | table | postgres | 8192 bytes |
pg_catalog | pg_default_acl | table | postgres | 0 bytes |
pg_catalog | pg_depend | table | postgres | 416 kB |
pg_catalog | pg_description | table | postgres | 288 kB |
pg_catalog | pg_enum | table | postgres | 0 bytes |
pg_catalog | pg_event_trigger | table | postgres | 0 bytes |
pg_catalog | pg_extension | table | postgres | 40 kB |
pg_catalog | pg_foreign_data_wrapper | table | postgres | 0 bytes |
pg_catalog | pg_foreign_server | table | postgres | 0 bytes |
pg_catalog | pg_foreign_table | table | postgres | 0 bytes |
pg_catalog | pg_index | table | postgres | 56 kB |
pg_catalog | pg_inherits | table | postgres | 0 bytes |
pg_catalog | pg_language | table | postgres | 40 kB |
pg_catalog | pg_largeobject | table | postgres | 0 bytes |
pg_catalog | pg_largeobject_metadata | table | postgres | 0 bytes |
pg_catalog | pg_namespace | table | postgres | 40 kB |
pg_catalog | pg_opclass | table | postgres | 48 kB |
pg_catalog | pg_operator | table | postgres | 144 kB |
pg_catalog | pg_opfamily | table | postgres | 48 kB |
pg_catalog | pg_pltemplate | table | postgres | 40 kB |
pg_catalog | pg_proc | table | postgres | 544 kB |
pg_catalog | pg_range | table | postgres | 40 kB |
pg_catalog | pg_rewrite | table | postgres | 512 kB |
pg_catalog | pg_seclabel | table | postgres | 8192 bytes |
pg_catalog | pg_shdepend | table | postgres | 40 kB |
pg_catalog | pg_shdescription | table | postgres | 48 kB |
pg_catalog | pg_shseclabel | table | postgres | 0 bytes |
pg_catalog | pg_statistic | table | postgres | 256 kB |
pg_catalog | pg_tablespace | table | postgres | 40 kB |
pg_catalog | pg_trigger | table | postgres | 48 kB |
pg_catalog | pg_ts_config | table | postgres | 40 kB |
pg_catalog | pg_ts_config_map | table | postgres | 48 kB |
pg_catalog | pg_ts_dict | table | postgres | 40 kB |
pg_catalog | pg_ts_parser | table | postgres | 40 kB |
pg_catalog | pg_ts_template | table | postgres | 40 kB |
pg_catalog | pg_type | table | postgres | 104 kB |
pg_catalog | pg_user_mapping | table | postgres | 0 bytes |
public | a1 | table | postgres | 40 kB |
public | bb | table | test | 744 kB |
public | cities | table | postgres | 40 kB |
public | f | table | postgres | 872 kB |
public | foo | table | postgres | 0 bytes |
public | mytable | table | postgres | 8192 bytes |
public | on_hand | table | postgres | 8192 bytes |
public | p | table | postgres | 792 kB |
public | sal_emp | table | postgres | 16 kB |
public | t1 | table | postgres | 16 kB |
public | test | table | postgres | 792 kB |
public | test1 | table | postgres | 792 kB |
public | testdate | table | postgres | 8192 bytes |
public | testtable | table | postgres | 8192 bytes |
public | testtrue | table | postgres | 16 kB |
public | weather | table | postgres | 40 kB |
(67 rows)
db1=# select now();
now
-------------------------------
2014-11-16 11:36:17.394054+00
(1 row)
db1=# select current_date;
date
------------
2014-11-16
(1 row)
db1=# select now()-current_date;
?column?
-----------------
11:38:09.004296
(1 row)
db1=# select now();
now
-------------------------------
2014-11-16 11:38:14.844081+00
(1 row)
db1=#
db117=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;
uptime
-----------------
3 days 22:37:18
(1 row)
db117=# select current_timestamp,pg_postmaster_start_time() ;
now | pg_postmaster_start_time
-------------------------------+------------------------------
2014-11-18 22:11:48.952187+08 | 2014-11-14 23:34:05.29886+08
(1 row)
^
db117=# select current_date;
date
------------
2014-11-18
(1 row)
^
db117=# select now();
now
-------------------------------
2014-11-18 22:12:33.448104+08
(1 row)
db117=# select current_timestamp;
now
-------------------------------
2014-11-18 22:12:51.963146+08
(1 row)
db117=# select pg_postmaster_start_time();
pg_postmaster_start_time
------------------------------
2014-11-14 23:34:05.29886+08
(1 row)
db117=#
root@TestLinux01 bin]# ./pg_controldata
pg_controldata: no data directory specified
Try "pg_controldata --help" for more information.
[root@TestLinux01 bin]# ./pg_controldata --help
pg_controldata displays control information of a PostgreSQL database cluster.
Usage:
pg_controldata [OPTION] [DATADIR]
Options:
-V, --version output version information, then exit
-?, --help show this help, then exit
If no data directory (DATADIR) is specified, the environment variable PGDATA
is used.
Report bugs to
[root@TestLinux01 bin]# ./pg_controldata /usr/local/pgsql/data
pg_control version number: 937
Catalog version number: 201306121
Database system identifier: 6081584468901461246
Database cluster state: in production
pg_control last modified: Tue 18 Nov 2014 10:42:20 PM CST
Latest checkpoint location: 0/1839470
Prior checkpoint location: 0/1839408
Latest checkpoint's REDO location: 0/1839470
Latest checkpoint's REDO WAL file: 000000010000000000000001
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/1814
Latest checkpoint's NextOID: 24576
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1799
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint: Tue 18 Nov 2014 10:42:20 PM CST
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
Current wal_level setting: minimal
Current max_connections setting: 100
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 64
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
[root@TestLinux01 bin]#
postgres=# \x
Expanded display is on.
postgres=# select * from pg_database;
-[ RECORD 1 ]-+------------------------------------
datname | template1
datdba | 10
encoding | 6
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
datistemplate | t
datallowconn | t
datconnlimit | -1
datlastsysoid | 12891
datfrozenxid | 1799
datminmxid | 1
dattablespace | 1663
datacl | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 2 ]-+------------------------------------
datname | template0
datdba | 10
encoding | 6
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
datistemplate | t
datallowconn | f
datconnlimit | -1
datlastsysoid | 12891
datfrozenxid | 1799
datminmxid | 1
dattablespace | 1663
datacl | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 3 ]-+------------------------------------
datname | postgres
datdba | 10
encoding | 6
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
datistemplate | f
datallowconn | t
datconnlimit | -1
datlastsysoid | 12891
datfrozenxid | 1799
datminmxid | 1
dattablespace | 1663
datacl |
-[ RECORD 4 ]-+------------------------------------
datname | db117
datdba | 10
encoding | 6
datcollate | en_US.UTF-8
datctype | en_US.UTF-8
datistemplate | f
datallowconn | t
datconnlimit | -1
datlastsysoid | 12891
datfrozenxid | 1799
datminmxid | 1
dattablespace | 1663
datacl |
postgres=#
SELECT count(*) FROM information_schema.tables
WHERE table_schema
NOT IN ('information_schema', 'pg_catalog');
postgres=# \d information_schema.tables
View "information_schema.tables"
Column | Type | Modifiers
------------------------------+-----------------------------------+-----------
table_catalog | information_schema.sql_identifier |
table_schema | information_schema.sql_identifier |
table_name | information_schema.sql_identifier |
table_type | information_schema.character_data |
self_referencing_column_name | information_schema.sql_identifier |
reference_generation | information_schema.character_data |
user_defined_type_catalog | information_schema.sql_identifier |
user_defined_type_schema | information_schema.sql_identifier |
user_defined_type_name | information_schema.sql_identifier |
is_insertable_into | information_schema.yes_or_no |
is_typed | information_schema.yes_or_no |
commit_action | information_schema.character_data |
View definition:
SELECT current_database()::information_schema.sql_identifier AS table_catalog,
nc.nspname::information_schema.sql_identifier AS table_schema,
c.relname::information_schema.sql_identifier AS table_name,
CASE
WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text
WHEN c.relkind = 'r'::"char" THEN 'BASE TABLE'::text
WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text
WHEN c.relkind = 'f'::"char" THEN 'FOREIGN TABLE'::text
ELSE NULL::text
END::information_schema.character_data AS table_type,
NULL::character varying::information_schema.sql_identifier AS self_referencing_column_name,
NULL::character varying::information_schema.character_data AS reference_generation,
CASE
WHEN t.typname IS NOT NULL THEN current_database()
ELSE NULL::name
END::information_schema.sql_identifier AS user_defined_type_catalog,
nt.nspname::information_schema.sql_identifier AS user_defined_type_schema,
t.typname::information_schema.sql_identifier AS user_defined_type_name,
CASE
WHEN c.relkind = 'r'::"char" OR (c.relkind = ANY (ARRAY['v'::"char", 'f'::"char"])) AND (pg_relation_is_updatable(c.oid::regclass, false) & 8) = 8 THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.yes_or_no AS is_insertable_into,
CASE
WHEN t.typname IS NOT NULL THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.yes_or_no AS is_typed,
NULL::character varying::information_schema.character_data AS commit_action
FROM pg_namespace nc
JOIN pg_class c ON nc.oid = c.relnamespace
LEFT JOIN (pg_type t
JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON c.reloftype = t.oid
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));
postgres=#