Cheat Sheet for Administrating a PostgreSQL Database/Server

PostgreSQL Interactive Terminal

 

Postgresql is similar to MySQL in that it uses an interactive terminal. To gain access type the following as root

su - postgres -c psql

You’re now logged into the Postgresql interactive terminal and interacting with the local server.

Common Shell Commands

 

Dump a database.

When dumping and restoring a database, you have to work within the postgres user, this is the default setup. The home directory for the postgres user is /var/lib/postgresql

pg_dump -U username database -f file.sql

 

Restore a database.

In order to restore the database you will need to ensure that the database name exist, for instance if it was dropped.

psql -U username -d database -f file.sql

 

Common Interactive Terminal Commands

 

Connect to a database, like “use database” in MySQL.

connect databasename;

 

View current databases on local server

select datname from pg_database;

 

View current databases on local server

\l

 

 Show current roles

select rolname from pg_roles;

 

Create a user.

create user ramesh with password 'tmppassword';

 

Create a database.

CREATE DATABASE mydb WITH OWNER ramesh;

 

Drop database.

DROP DATABASE mydb;

If you notice that you’re unable to drop a database because of connections, then run the following.

SELECT
pg_terminate_backend(procpid)
FROM
pg_stat_activity
WHERE -- don't kill my own connection!
procpid <> pg_backend_pid();

If it’s a busy database then you may need to run the following first.

REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;
0 Shares:
You May Also Like

QuickPwn Release Allows Fast Jailbreak Of iPhone Firmware Update 2.0.2

That's right, hot off the blog presses QuickPwn has been updated by the iphone-dev.org team and now allows you to Jailbreak your iPhones 2.0.2 firmware. The new 2.0.2 firmware that was released on Monday supposedly fixes some "bugs", however the bugs aren't mentioned anywhere.
Here is the updated QuickPwn for Windows, wrapped by Poorlad’s GUI. It contains our new bundles for 2.0.2 and we’ve added support for version 2.0 devices which means you can QuickPwn and jailbreak the device if it is running 2.0, 2.0.1 or 2.0.2. Remember this is still beta software, so usual rules apply, no complaints ifanything goes wrong and use the tool at at your own risk! Download here! SHA1 = 8e1ed2ce9e7e473d38a9dc7824a384a9ac34d7d0

Read the full article at blog.iphone-dev.org
Download the new QuickPwn 1.2.0 with the new 2.0.2 bundle.

Thanks for another successful Jailbreak!