# show a list of databases
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
R -- RULE
x -- REFERENCES (foreign keys)
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
T -- TEMPORARY
arwdRxt -- ALL PRIVILEGES (for tables)
* -- grant option for preceding privilege
/yyyy -- user who granted this privilege
# Run the vacuum utility
Note: vacuum reclaims space from deleted records and updates indexes. It should be set up in cron. Newer versions of postgresql may run vacuum automatically.
Increase perfomance with shared memory
One effective performance tuning tip for Postgresql is to increase the shared
memory buffers. This might require adding RAM to the server. Many Linux distros
default to 32MB of shared memory, controlled by two kernel parameters:
/proc/sys/kernel/shmmax
/proc/sys/kernel/shmall
These values can be changed at run time, but it is better to set them at boot
using the /etc/sysctl.conf file. This increases shared memory to 1GB:
# increase shared buffers for postgres at boot
kernel.shmmax=1073741824
kernel.shmall=1073741824
Then, tell PostgreSQL to use 768MB of the 1GB available in the
/var/lib/pgsql/data/postgresql.conf file:
shared_buffers = 98304 # min 16, at least max_connections*2, 8KB each
Restart PostgreSQL for the change to take effect.
\l# show all users
select * from pg_user;# show all tables (including system tables)
select * from pg_tables;# show tables in the current context (database/schema)
\d# change current database
\c database;# show all schemas in the current database
\dn# Grant permissions on a schema to a user
GRANT ALL ON myschema TO user;# show help
\?# copy a table to a tab delimeted file
COPY table TO 'table.txt';# load a table from a tab delimeted file
COPY table FROM 'table.txt';# show permissions on database objects
\z [object]
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
R -- RULE
x -- REFERENCES (foreign keys)
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
T -- TEMPORARY
arwdRxt -- ALL PRIVILEGES (for tables)
* -- grant option for preceding privilege
/yyyy -- user who granted this privilege
# Run the vacuum utility
vacuumdb --verbose --analyze --all
Note: vacuum reclaims space from deleted records and updates indexes. It should be set up in cron. Newer versions of postgresql may run vacuum automatically.
Increase perfomance with shared memory
One effective performance tuning tip for Postgresql is to increase the shared
memory buffers. This might require adding RAM to the server. Many Linux distros
default to 32MB of shared memory, controlled by two kernel parameters:
/proc/sys/kernel/shmmax
/proc/sys/kernel/shmall
These values can be changed at run time, but it is better to set them at boot
using the /etc/sysctl.conf file. This increases shared memory to 1GB:
# increase shared buffers for postgres at boot
kernel.shmmax=1073741824
kernel.shmall=1073741824
Then, tell PostgreSQL to use 768MB of the 1GB available in the
/var/lib/pgsql/data/postgresql.conf file:
shared_buffers = 98304 # min 16, at least max_connections*2, 8KB each
Restart PostgreSQL for the change to take effect.
No comments:
Post a Comment