Basic Interactivity
- List all databases
PgSQL
1 | \list |
PgSQL
1 | \l |
- Switch Database
PgSQL
1 | \connect <database_name> |
- List all tables
PgSQL
1 | \d |
- Describe Table
PgSQL
1 | \d+ table_name |
Read More: https://stackoverflow.com/questions/15238034/why-psql-cant-find-relation-name-for-existing-table
- Create Database
PgSQL
1 | CREATE DATABASE reporting; |
Grant Access to User
The user needs access to the database, obviously:
PgSQL
1 | GRANT CONNECT ON DATABASE my_db TO my_user; |
And (at least) the USAGE privilege on the schema:
PgSQL
1 | GRANT USAGE ON SCHEMA public TO my_user; |
Then, all permissions for all tables (requires Postgres 9.0 or later):
PgSQL
1 | GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user; |
And don’t forget sequences (if any):
PgSQL
1 | GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user; |
Backup and Restore Database
- Backup database into file
PgSQL
1 | pg_dump -U postgres databasename > filename.sql |
- Restore Database from file
PgSQL
1 | pg_restore -U postgres -d databasename -1 filename.sql |
Installing PostGIS
– Check if PostGIS is installed
PgSQL
1 | SELECT PostGIS_full_version(); |
If PostGIS is not installed, you will see the following output:
1 | ERROR: function postgis_full_version() does not exist |
Otherwise, you will see the following:
1 2 3 | POSTGIS="2.0.1 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.1, released 2012/05/15 GDAL_DATA not found" LIBXML="2.7.6" TOPOLOGY RASTER (1 row) |
That’s all for know. Let me know if there is anything extra to add up on the list.
Recent Posts
Recent Comments
- LogixTree Networks on Localhost showing “Index/of” instead of displaying the Website
- AMANUR RAHMAN on Localhost showing “Index/of” instead of displaying the Website
- Christy on Support Purchase Notes in Order Emails WooCommerce
- LogixTree Networks on Localhost showing “Index/of” instead of displaying the Website
- Sofia on Localhost showing “Index/of” instead of displaying the Website