Users and groups of users are shared across the entire cluster, but no other data is shared across databases. SchemasĪ PostgreSQL database cluster contains one or more named databases. If the system has syslog already set up to handle log rotation, it may be desirable to set log_destination to syslog in nf. PostgreSQL has a built-in log rotation feature, or use the system log rotation. REINDEX can be used safely and easily, althought the command requires an exclusive table lock. Reindexing improves speed by moving logically adjacent pages to also be physically adjacent. Indexes become less efficient after being heavily modified.Reindexing reallocates mostly empty pages. Index pages remain allocated even only a few index keys have not been deleted.On Debian, the postgresql package appears to enable autovacuum to default. PostgreSQL offers an optional but recommended autovacuum feature that automates VACUUM and ANALYZE.īecause it uses the stats to optimize its operations, track_counts must be set true for autovacuum to work.Ī persistent daemon, the autovacuum launcher, starts autovacuum workder processes. FULL VACUUM reclaims more space, but runs more slowly and locks tables.Normal VACUUM can run during regular production database operations (apart from ALTER TABLE not being available).guard against data loss from transaction and multixact ID wraparound.updates the visibility map to speed up index-only scans.updates stats used by the PostgreSQL query planner.recovers space from updated or deleted rows.? ~ $ gunzip -c filename.gz | psql dbname Since pg_dump writes to STDOUT, things like this work: ? ~ $ pg_dump dbname | gzip > filename.gz (Actually, you can specify any existing database name to start from, but if you are loading into an empty cluster then postgres should usually be used.) ? ~ $ sudo -u postgres psql -f dumpfile postgres ? ~ $ sudo -u postgres psql mydb dumpfile ? ~ $ sudo -u postgres pg_dump mydb > dumpfile.sql ? ~ $ sudo -u postgres psql mydb CREATE DATABASE dbname TEMPLATE template0 įrom the SQL environment, or from the shell: ? ~ $ createdb -T template0 dbname Postgres=# GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser Postgres=# CREATE USER myuser WITH ENCRYPTED PASSWORD 'mysecret' => ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public => GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO mygrp => GRANT ALL ON ALL TABLES IN SCHEMA public TO mygrp => GRANT USAGE ON SCHEMA public TO mygrp => GRANT CONNECT ON DATABASE mydb TO mygrp => REVOKE ALL ON DATABASE mydb FROM public
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |