Creating new user.
CREATE USER davide WITH PASSWORD 'jw8s0F4';
further details here
Change user password
ALTER USER admin WITH PASSWORD 'admin';
making a user as superuser
ALTER USER username WITH SUPERUSER;
further details here
check the list of users:
(select string_agg(d.datname, ',' order by d.datname)
from pg_database d
where has_database_privilege(u.usename, d.datname, 'CONNECT')) as allowed_databases
from pg_user u
order by u.usename
Alter table and add column
ALTER TABLE public.nseequitydata
ADD COLUMN splitfactor bigint NOT NULL DEFAULT 1;
Login to postgreSQL
sudo su - postgres
[sudo] password for conquistador:
|Show all users
|Show all databases
|Connect to database
||postgres=# \connect equityindia
|Show tables details
|Show to remote postgresql database
||psql -h host -d database -U user -W
I am working with huge data set and its growing everyday. Fear of loosing data which you have gathered after so much efforts can give you sleepless nights. One of the way to tack care of this risk is by taking backup frequently, whenever you need reload the data, you can simply reload it.
Creating backup file
pg_dump dbname > outfile
Loading from back up file
psql dbname < infile
please note that dbname needs to be created prior to loading this backup file and it will not only create database in the same format but also it will load data.
Dealing with huge datasets
If your are dealing with huge databses you can takke dump in zip file
pg_dump dbname | gzip > filename.gz
and load it as below
gunzip -c filename.gz | psql dbname
cat filename.gz | gunzip | psql dbname
alternatively you can split files if you are not comfortable with zip or have limitation of file size.
pg_dump dbname | split -b 1m - filename
cat filename* | psql dbname
pgadmin3 can be installed using following command
sudo apt-get install pgadmin3
You can invoke pgadmin3 GUI from main menu as well as by typing following command.
From file menu, please use ‘add server’ and enter data as below:
If you facing authentication issue, please use following commands to reset the password.
$sudo -u postgres psql postgres
Type "help" for help.
postgres=# password postgres
Enter new password:
Enter it again: