Backup: $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

Restore: $ psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

1. Как изменить root пароль в PostgreSQL?

$ /usr/local/pgsql/bin/psql postgres postgres
Password: (oldpassword)
# ALTER USER postgres WITH PASSWORD ‘tmppassword’;
$ /usr/local/pgsql/bin/psql postgres postgres
Password: (tmppassword)

Изменение пароля для обычного пользователя происходит таким же образом. Пользователь root может поменять пароль любому пользователю.

# ALTER USER username WITH PASSWORD ‘tmppassword’;

2. Как установить PostgreSQL в автозапуск?

$ su – root
# tar xvfz postgresql-8.3.7.tar.gz
# cd postgresql-8.3.7
# cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql
# chmod a+x /etc/rc.d/init.d/postgresql

3. Проверяем состояние сервера

$ /etc/init.d/postgresql status
Password:
pg_ctl: server is running (PID: 6171)
/usr/local/pgsql/bin/postgres “-D” “/usr/local/pgsql/data”
[Замечание: Это сообщение говорит о том, что сервер запущен и работате нормально]
$ /etc/init.d/postgresql status
Password:
pg_ctl: no server running
[Замечание: Это сообщение готоворит о том, что сервер не запущен]

4. Как запустить, остановить, перезапустить PostgreSQL?

# service postgresql stop
Stopping PostgreSQL: server stopped
ok
# service postgresql start
Starting PostgreSQL: ok
# service postgresql restart
Restarting PostgreSQL: server stopped
ok

5. Как посмотреть какая версия PostgreSQL запущена?

$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=# select version();
version
—————————————————————————————————-
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)
test=#

5. Как создать пользователя в PostgreSQL?

Для этого существуют два метода..

Метод 1: Создаем пользователя в через PSQL шелл, командой CREATE USER.

# CREATE USER ramesh WITH password ‘tmppassword’;
CREATE ROLE

Метод 2: Создаем пользователя в через шелл команду createuser.

$ /usr/local/pgsql/bin/createuser sathiya
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE

6. Как создать базу в PostgreSQL ?

Для этого существует 2 метода.

Метод 1: Создаем базу черезе PSQL шелл, с помощью команды CREATE DATABASE.

# CREATE DATABASE mydb WITH OWNER ramesh;
CREATE DATABASE

Метод 2: Используем команду createdb.

$ /usr/local/pgsql/bin/createdb mydb -O ramesh
CREATE DATABASE

7. Получаем список всех баз в Postgresql?

# \l
List of databases
Name | Owner | Encoding
———-+———-+———-
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

8. Как удалить базу в PostgreSQL?

# \l
List of databases
Name | Owner | Encoding
———-+———-+———-
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
# DROP DATABASE mydb;
DROP DATABASE

9. Пользуемя встроенным хелпом к командам

Команда \? отобразит строку помощи для команда PSQL. \h CREATE покажет хелп для всех команд который начинаются с CREATE.

# \?

# \h CREATE
# \h CREATE INDEX

10. Как получить список всех таблиц в базе данный в Postgresql?

# \d

Для пустой базы вы получите сообщение “No relations found.”

11. Как узнать время выполнения запроса?

# \timing — после выполения данной команды каждый последующий запрос будет показывать время выполнения.

# \timing
Timing is on.
# SELECT * from pg_catalog.pg_attribute ;
Time: 9.583 ms

12. Как бэкапить и восстанавливать базы и таблицы в PostgreSQL?

Этот вопрос довольно велик и я опубликую его позднее отдельной статьей.
13. Как посмотреть список доступных функций в PostgreSQL ?

Для того чтобы получить список доступных функций, скажите \df+

# \df

# \df+

14. Как отредактировать запрос к PostgreSQL в редакторе?

# \e

\e откроет редактор, в котором вы можете отредактировать запрос и сохранить его.
15. Где я могу найти файл истории postgreSQL?

Подобно файлу ~/.bash_history, postgreSQL хранит все sql команды в файле ~/.psql_history.

$ cat ~/.psql_history
alter user postgres with password ‘tmppassword’;
\h alter user
select version();
create user ramesh with password ‘tmppassword’;
\timing
select * from pg_catalog.pg_attribute;

How To Backup Postgres Database
1. Backup a single postgres database

This example will backup erp database that belongs to user geekstuff, to the file mydb.sql

$ pg_dump -U geekstuff erp -f mydb.sql

It prompts for password, after authentication mydb.sql got created with create table, alter table and copy commands for all the tables in the erp database. Following is a partial output of mydb.sql showing the dump information of employee_details table.

—
— Name: employee_details; Type: TABLE; Schema: public; Owner: geekstuff; Tablespace:
—

CREATE TABLE employee_details (
employee_name character varying(100),
emp_id integer NOT NULL,
designation character varying(50),
comments text
);

ALTER TABLE public.employee_details OWNER TO geekstuff;

—
— Data for Name: employee_details; Type: TABLE DATA; Schema: public; Owner: geekstuff
—
COPY employee_details (employee_name, emp_id, designation, comments) FROM stdin;
geekstuff 1001 trainer
ramesh 1002 author
sathiya 1003 reader
\.
—
— Name: employee_details_pkey; Type: CONSTRAINT; Schema: public; Owner: geekstuff; Tablespace:
—
ALTER TABLE ONLY employee_details

ADD CONSTRAINT employee_details_pkey PRIMARY KEY (emp_id);

2. Backup all postgres databases

To backup all databases, list out all the available databases as shown below.
Login as postgres / psql user:

$ su postgres

List the databases:

$ psql -l

List of databases
Name | Owner | Encoding
———–+———–+———-
article | sathiya | UTF8
backup | postgres | UTF8
erp | geekstuff | UTF8
geeker | sathiya | UTF8

Backup all postgres databases using pg_dumpall:

You can backup all the databases using pg_dumpall command.

$ pg_dumpall > all.sql

Verify the backup:

Verify whether all the databases are backed up,

$ grep “^[\]connect” all.sql
\connect article
\connect backup
\connect erp
\connect geeker

3. Backup a specific postgres table

$ pg_dump –table products -U geekstuff article -f onlytable.sql

To backup a specific table, use the –table TABLENAME option in the pg_dump command. If there are same table names in different schema then use the –schema SCHEMANAME option.
How To Restore Postgres Database
1. Restore a postgres database

$ psql -U erp -d erp_devel -f mydb.sql

This restores the dumped database to the erp_devel database.
Restore error messages

While restoring, there may be following errors and warning, which can be ignored.

psql:mydb.sql:13: ERROR: must be owner of schema public
psql:mydb.sql:34: ERROR: must be member of role “geekstuff”
psql:mydb.sql:59: WARNING: no privileges could be revoked
psql:mydb.sql:60: WARNING: no privileges could be revoked
psql:mydb.sql:61: WARNING: no privileges were granted
psql:mydb.sql:62: WARNING: no privileges were granted

2. Backup a local postgres database and restore to remote server using single command:

$ pg_dump dbname | psql -h hostname dbname

The above dumps the local database, and extracts it at the given hostname.
3. Restore all the postgres databases

$ su postgres
$ psql -f alldb.sql

4. Restore a single postgres table

The following psql command installs the product table in the geek stuff database.

$ psql -f producttable.sql geekstuff

ALTER ROLE USER_NAME WITH SUPERUSER CREATEDB LOGIN;

Drop Connections to a database before drop :
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
AND pid <> pg_backend_pid();

Tagged with: pg_admin | pg_ctl | pg_dump | pg_dumpall | pgsql | psql

Categorised as: Linux