1. 先研究一下 createdb
C:\Users\Maxwell Pan>createdb --help
createdb creates a PostgreSQL database.
Usage:
createdb [OPTION]... [DBNAME] [DESCRIPTION]
Options:
-D, --tablespace=TABLESPACE default tablespace for the database
-e, --echo show the commands being sent to the server
-E, --encoding=ENCODING encoding for the database
-l, --locale=LOCALE locale settings for the database
--lc-collate=LOCALE LC_COLLATE setting for the database
--lc-ctype=LOCALE LC_CTYPE setting for the database
--icu-locale=LOCALE ICU locale setting for the database
--locale-provider={libc|icu}
locale provider for the database's default collation
-O, --owner=OWNER database user to own the new database
-S, --strategy=STRATEGY database creation strategy wal_log or file_copy
-T, --template=TEMPLATE template database to copy
-V, --version output version information, then exit
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
--maintenance-db=DBNAME alternate maintenance database
By default, a database with the same name as the current user is created.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
C:\Users\Maxwell Pan>2. 创建数据库
C:\Users\Maxwell Pan>createdb -U postgres samson
Password:
Password:
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
employees | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
samson | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
temp | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(6 rows)
postgres=# create database roger owner postgres;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
employees | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
roger | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
samson | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
temp | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(7 rows)
postgres=#3.确认数据库
postgres=# select datname,oid from pg_database;
datname | oid
-----------+-------
postgres | 5
template1 | 1
template0 | 4
employees | 24577
temp | 24578
samson | 24579
roger | 24580
(7 rows)
postgres=#
4. 登录samson数据库是无法进行drop database，需要postgres数据库。
postgres=# select datname,oid from pg_database;
datname | oid
-----------+-------
postgres | 5
template1 | 1
template0 | 4
employees | 24577
temp | 24578
samson | 24579
roger | 24580
(7 rows)
postgres=# \q
C:\Users\Maxwell Pan>psql -U postgres samson
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
samson=# drop database samson;
ERROR: cannot drop the currently open database
samson=#
samson=# \conninfo
You are connected to database "samson" as user "postgres" on host "localhost" (address "::1") at port "5432".
samson=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=#
postgres=# drop database samson;
DROP DATABASE
postgres=#
postgres=#5. dropdb的使用方法
C:\Users\Maxwell Pan>
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
employees | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
roger | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
temp | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(6 rows)
postgres=# \q
C:\Users\Maxwell Pan>dropdb -U postgres roger
Password:
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
employees | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
temp | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(5 rows)
postgres=#6. 研究一下dropdb
C:\Users\Maxwell Pan>
C:\Users\Maxwell Pan>dropdb --help
dropdb removes a PostgreSQL database.
Usage:
dropdb [OPTION]... DBNAME
Options:
-e, --echo show the commands being sent to the server
-f, --force try to terminate other connections before dropping
-i, --interactive prompt before deleting anything
-V, --version output version information, then exit
--if-exists don't report error if database doesn't exist
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
--maintenance-db=DBNAME alternate maintenance database
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
C:\Users\Maxwell Pan>7. Create User
Linux 下 创建用户
[root@postgres ~]#
[root@postgres ~]# su - postgres
[postgres@postgres ~]$ createuser test1
[postgres@postgres ~]$ psql
psql (13.9)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test1 | | {}
postgres=#
8. 研究一下 createuser --help in windows
C:\Users\Maxwell Pan>
C:\Users\Maxwell Pan>pg_ctl status
pg_ctl: server is running (PID: 9796)
D:/Soft/PostgreSQL15/bin/postgres.exe
C:\Users\Maxwell Pan>createuser --help
createuser creates a new PostgreSQL role.
Usage:
createuser [OPTION]... [ROLENAME]
Options:
-c, --connection-limit=N connection limit for role (default: no limit)
-d, --createdb role can create new databases
-D, --no-createdb role cannot create databases (default)
-e, --echo show the commands being sent to the server
-g, --role=ROLE new role will be a member of this role
-i, --inherit role inherits privileges of roles it is a
member of (default)
-I, --no-inherit role does not inherit privileges
-l, --login role can login (default)
-L, --no-login role cannot login
-P, --pwprompt assign a password to new role
-r, --createrole role can create new roles
-R, --no-createrole role cannot create roles (default)
-s, --superuser role will be superuser
-S, --no-superuser role will not be superuser (default)
-V, --version output version information, then exit
--interactive prompt for missing role name and attributes rather
than using defaults
--replication role can initiate replication
--no-replication role cannot initiate replication
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as (not the one to create)
-w, --no-password never prompt for password
-W, --password force password prompt
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
C:\Users\Maxwell Pan>9. windows 创建user
C:\Users\Maxwell Pan>
C:\Users\Maxwell Pan>createuser -U postgres -P -S malcolm
Enter password for new role:
Enter it again:
Password:
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=#
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
malcolm | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#10. Linux 下创建user
[postgres@postgres ~]$
[postgres@postgres ~]$ createuser -P -S malcolm
Enter password for new role:
Enter it again:
[postgres@postgres ~]$ psql
psql (13.9)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
malcolm | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test1 | | {}
postgres=#
11. Windows 下创建超级用户
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# create user scott login superuser password 'tiger';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
malcolm | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
scott | Superuser | {}
postgres=# \q
C:\Users\Maxwell Pan>12. 指定用户和数据库登录
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
nano | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
postgres=# \q
C:\Users\Maxwell Pan>psql -U malcolm -d nano
Password for user malcolm:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=> \q
C:\Users\Maxwell Pan>psql -U malcolm -d postgres
Password for user malcolm:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=> \q
C:\Users\Maxwell Pan>13. windows下创建普通用户角色
C:\Users\Maxwell Pan>createuser -U postgres --interactive
Enter name of role to add: henry
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
Password:
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=#
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
henry | Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
scott | Superuser | {}
postgres=#13. windows 删除用户
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
nano | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
postgres=# revoke connect on database nano from public;
REVOKE
postgres=# \q
C:\Users\Maxwell Pan>psql -U postgres -d nano
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=#
nano=# \q
C:\Users\Maxwell Pan>dropuser -U postgres malcolm
Password:
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=#
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
henry | Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
scott | Superuser | {}
postgres=# drop user henry;
DROP ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
scott | Superuser | {}
postgres=#14. Create Schema
C:\Users\Maxwell Pan>psql -U scott -d nano
Password for user scott:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=# create table vehicle(des varchar(100));
CREATE TABLE
nano=# insert into vehicle values('this is a public car');
INSERT 0 1
nano=# select * from vehicle;
des
----------------------
this is a public car
(1 row)
nano=# create schema hr;
CREATE SCHEMA
nano=# \dn
List of schemas
Name | Owner
--------+-------------------
hr | scott
public | pg_database_owner
(2 rows)
nano=# create table hr.vehicle(des varchar(100));
CREATE TABLE
nano=# insert into hr.vehicle values('this is a hr car');
INSERT 0 1
nano=# create schema authorization scott;
CREATE SCHEMA
nano=# \dn
List of schemas
Name | Owner
--------+-------------------
hr | scott
public | pg_database_owner
scott | scott
(3 rows)
nano=# create table scott.vehicle(des varchar(100));
CREATE TABLE
nano=# insert into scott.vehicle values('this is a scott car');
INSERT 0 1
nano=# select * from pg_tables where tablename='vehicle';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
public | vehicle | scott | | f | f | f | f
hr | vehicle | scott | | f | f | f | f
scott | vehicle | scott | | f | f | f | f
(3 rows)
nano=# \q
C:\Users\Maxwell Pan>15. Drop Schema
C:\Users\Maxwell Pan>
C:\Users\Maxwell Pan>psql -U scott -d nano
Password for user scott:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=# select * from vehicle;
des
---------------------
this is a scott car
(1 row)
nano=# show serarch_path;
ERROR: unrecognized configuration parameter "serarch_path"
nano=# show search_path;
search_path
-----------------
"$user", public
(1 row)
nano=# drop table scott.vehicle;
DROP TABLE
nano=# select * from vehicle;
des
----------------------
this is a public car
(1 row)
nano=# select * from hr.vehicle;
des
------------------
this is a hr car
(1 row)
nano=# \q
C:\Users\Maxwell Pan>psql -U scott -d nano
Password for user scott:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=# drop schema scott;
DROP SCHEMA
nano=# \dn
List of schemas
Name | Owner
--------+-------------------
hr | scott
public | pg_database_owner
(2 rows)
nano=#
nano=# select * from hr.vehicle;
des
------------------
this is a hr car
(1 row)
nano=# drop schema hr;
ERROR: cannot drop schema hr because other objects depend on it
DETAIL: table hr.vehicle depends on schema hr
HINT: Use DROP ... CASCADE to drop the dependent objects too.
nano=#
nano=# drop schema hr cascade;
NOTICE: drop cascades to table hr.vehicle
DROP SCHEMA
nano=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
nano=# select * from hr.vehicle;
ERROR: relation "hr.vehicle" does not exist
LINE 1: select * from hr.vehicle;
^
nano=#16.Search Schema path
C:\Users\Maxwell Pan>
C:\Users\Maxwell Pan>psql -U scott -d nano
Password for user scott:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=# select * from vehicle;
des
---------------------
this is a scott car
(1 row)
nano=# show serarch_path;
ERROR: unrecognized configuration parameter "serarch_path"
nano=# show search_path;
search_path
-----------------
"$user", public
(1 row)17.GRANT rivileges on Database Objects
C:\Users\Maxwell Pan>psql -U sam -d nano
Password for user sam:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=> create table tree(type varchar(100));
ERROR: permission denied for schema public
LINE 1: create table tree(type varchar(100));
^
nano=> \c nano postgres
Password for user postgres:
You are now connected to database "nano" as user "postgres".
nano=# grant all on schema public to sam;
GRANT
nano=# \c nano sam
Password for user sam:
You are now connected to database "nano" as user "sam".
nano=> \d
Did not find any relations.
nano=> create table tree(type varchar(100));
CREATE TABLE
nano=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | tree | table | sam
(1 row)
nano=> insert into tree values('this is a olive tree');
INSERT 0 1
nano=>
nano=>
C:\Users\Maxwell Pan>psql -U sam -d nano
Password for user sam:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=> create table tree(type varchar(100));
ERROR: relation "tree" already exists
nano=> select * from tree;
type
----------------------
this is a olive tree
(1 row)
nano=> \q
C:\Users\Maxwell Pan>C:\Users\Maxwell Pan>psql -U ronk -d nano
Password for user ronk:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=> select * from tree;
ERROR: permission denied for table tree
nano=> \q
C:\Users\Maxwell Pan>psql -U sam -d nano
Password for user sam:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=> grant select on tree to ronk;
GRANT
nano=> exit
C:\Users\Maxwell Pan>psql -U ronk -d nano
Password for user ronk:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=> select * from tree;
type
----------------------
this is a olive tree
(1 row)
nano=> insert into tree values('this is a banayan tree');
ERROR: permission denied for table tree
nano=> \q18.Revoke privileges on Database Objects
C:\Users\Maxwell Pan>psql -U sam -d nano
Password for user sam:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=> revoke select on tree from ronk;
REVOKE
nano=> \q
C:\Users\Maxwell Pan>psql -U ronk -d nano
Password for user ronk:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
nano=> select * from tree;
ERROR: permission denied for table tree
nano=>
