Tag: PostgreSQL – ServerOK

Run PostgreSQL in Docker

June 30, 2019

by ServerOk with No Comment Docker

To run PostgreSQL on docker, create a directory for saving the data presistant

mkdir -p /opt/my-postgresql

1	mkdir -p /opt/my-postgresql

run docker container

docker run --name my-postgresql \
    -p 5432:5432 \
    -e POSTGRES_PASSWORD=serverok123 \
    -e POSTGRES_USER=serverok \
    -e POSTGRES_DB=serverok \
    -v /opt/my-postgresql:/var/lib/postgresql/data \
    -d postgres

docker run --name my-postgresql \

-p 5432:5432 \

-e POSTGRES_PASSWORD=serverok123 \

-e POSTGRES_USER=serverok \

-e POSTGRES_DB=serverok \

-v /opt/my-postgresql:/var/lib/postgresql/data \

-d postgres

In above, change the value for POSTGRES_DB, POSTGRES_USER and POSTGRES_PASSWORD as needed.

Connect to PostgreSQL server

To connect to PostgreSQL server, run

docker container exec -ti my-postgresql bash

1	docker container exec -ti my-postgresql bash

Now you are inside PostgreSQL conainer, to login, run

psql -U serverok -W

1	psql -U serverok -W

It will ask for password. Once you enter password, you will be in PostgreSQL command line.

See docker

PostgreSQL Export table into CSV file

June 5, 2019

by ServerOk with No Comment PostgreSQL

To export a table into CSV file, run

\copy TABLE_NAME to 'FILE_NAME.csv' csv header;

1	\copy TABLE_NAME to 'FILE_NAME.csv' csv header;

This will save the CSV file in current directory.

PostgreSQL List all databases

February 7, 2019

by ServerOk with No Comment PostgreSQL

To list all databases in PostgreSQL database, first connect to PostgreSQL database using psql command

su - postgres
psql

1 2	su - postgres psql

Now run

\list

\list

This will list all available databases.

To change to a database, use command /connect

\connect DBNAME

1	\connect DBNAME

Once you connected to a database, you can list all tables in a database with command

\dt

\dt

PostgreSQL Create Database

November 17, 2018

by ServerOk with No Comment PostgreSQL

To create a database, run

CREATE DATABASE "DB_NAME_HERE";

1	CREATE DATABASE "DB_NAME_HERE";

Creating user, grant permissions

CREATE USER USER_NAME_HERE WITH PASSWORD 'BAPT788HZutBdV';

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO USER_NAME_HERE;
GRANT USAGE ON SCHEMA public TO USER_NAME_HERE;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA DB_NAME_HERE TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA DB_NAME_HERE TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON DATABASE DB_NAME_HERE TO USER_NAME_HERE;

psql DB_NAME_HERE -c "GRANT ALL ON ALL TABLES IN SCHEMA public to USER_NAME_HERE;"
psql DB_NAME_HERE -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to USER_NAME_HERE;"
psql DB_NAME_HERE -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to USER_NAME_HERE;"

CREATE USER USER_NAME_HERE WITH PASSWORD 'BAPT788HZutBdV';

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO USER_NAME_HERE;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO USER_NAME_HERE;

GRANT USAGE ON SCHEMA public TO USER_NAME_HERE;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA DB_NAME_HERE TO USER_NAME_HERE;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA DB_NAME_HERE TO USER_NAME_HERE;

GRANT ALL PRIVILEGES ON DATABASE DB_NAME_HERE TO USER_NAME_HERE;

psql DB_NAME_HERE -c "GRANT ALL ON ALL TABLES IN SCHEMA public to USER_NAME_HERE;"

psql DB_NAME_HERE -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to USER_NAME_HERE;"

psql DB_NAME_HERE -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to USER_NAME_HERE;"

PostgreSQL Delete a Database

November 17, 2018

by ServerOk with No Comment PostgreSQL

To delete a PostgreSQL database, run

DROP DATABASE DB_NAME;

1	DROP DATABASE DB_NAME;

Example

DROP DATABASE "odoodb2";

1	DROP DATABASE "odoodb2";

Backup PostgreSQL Database

June 6, 2018

by ServerOk with No Comment Linux

To backup PostgreSQL database, run

pg_dump  -h HOSTNMAME -p PORT -U USER -d DBNAME -W -f file.psql

1	pg_dump -h HOSTNMAME -p PORT -U USER -d DBNAME -W -f file.psql

Example

pg_dump  -h ukgaa1vy7o672pj.caqdhl3x5dow.us-east-1.rds.amazonaws.com -p 5432 -U serveroku -d serverokdb -W -f file.psql

1	pg_dump -h ukgaa1vy7o672pj.caqdhl3x5dow.us-east-1.rds.amazonaws.com -p 5432 -U serveroku -d serverokdb -W -f file.psql

PostgreSQL

Create user in PostgreSQL

April 8, 2018

by ServerOk with No Comment Linux

Method 1

To create user, become user “postgres” with

su - postgres

1	su - postgres

now run

createuser USER_NAME

1	createuser USER_NAME

Method 2

You can run following commands in psql prompt

CREATE USER USER_NAME_HERE WITH PASSWORD 'PASSWORD_HERE';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO USER_NAME_HERE;
GRANT USAGE ON SCHEMA public TO USER_NAME_HERE;

CREATE USER USER_NAME_HERE WITH PASSWORD 'PASSWORD_HERE';

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO USER_NAME_HERE;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO USER_NAME_HERE;

GRANT USAGE ON SCHEMA public TO USER_NAME_HERE;

https://www.postgresql.org/docs/8.0/static/sql-createuser.html

PostgreSQL

pg_ctl

March 10, 2018

by ServerOk with No Comment PostgreSQL

pg_ctl is used to start, stop PostgreSQL server.

To start PostgreSQL, run

pg_ctl start -D /path/to/datadir

1	pg_ctl start -D /path/to/datadir

You can also use stop/restart.

PostgreSQL

February 4, 2018

by ServerOk with No Comment Linux

To install PostgreSQL on Ubuntu/Debian, run

apt-get install -y postgresql

1	apt-get install -y postgresql

Start it with

service postgresql start

1	service postgresql start

By default user “postgres” have full access on PostgreSQL. To create a user, run

su postgres
createuser -P USERNAME

1 2	su postgres createuser -P USERNAME

To create database, run

createdb -T template0 -E UTF8 -O USERNAME DBNAME

1	createdb -T template0 -E UTF8 -O USERNAME DBNAME

You can see help with

postgres@ok-vm:~$ createdb --help
createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database

By default, a database with the same name as the current user is created.

Report bugs to <pgsql-bugs@postgresql.org>.
postgres@ok-vm:~$

postgres@ok-vm:~$ createdb --help

createdb creates a PostgreSQL database.

Usage:

createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:

-D, --tablespace=TABLESPACE default tablespace for the database

-e, --echo show the commands being sent to the server

-E, --encoding=ENCODING encoding for the database

-l, --locale=LOCALE locale settings for the database

--lc-collate=LOCALE LC_COLLATE setting for the database

--lc-ctype=LOCALE LC_CTYPE setting for the database

-O, --owner=OWNER database user to own the new database

-T, --template=TEMPLATE template database to copy

-V, --version output version information, then exit

-?, --help show this help, then exit

Connection options:

-h, --host=HOSTNAME database server host or socket directory

-p, --port=PORT database server port

-U, --username=USERNAME user name to connect as

-w, --no-password never prompt for password

-W, --password force password prompt

--maintenance-db=DBNAME alternate maintenance database

By default, a database with the same name as the current user is created.

Report bugs to <pgsql-bugs@postgresql.org>.

postgres@ok-vm:~$