PostgreSQL - Revision history

Kevin: /* Restore Database */

2016-01-02T01:00:19Z

Restore Database

← Older revision		Revision as of 01:00, 2 January 2016
Line 364:		Line 364:
	e.g.)		e.g.)
	<pre>		<pre>
-	$ sudo -u postgres psql -f my_db_dump.sql postgres	+	$ sudo -u postgres psql --set ON_ERROR_STOP=on -f my_db_dump.sql postgres
	</pre>		</pre>

2016-01-01T16:18:16Z

Restore Database

← Older revision		Revision as of 16:18, 1 January 2016
Line 357:		Line 357:
	</pre>		</pre>

		+	* Restore all databases backed up by pg_dumpall
		+	<pre>
		+	psql -f FILE postgres
		+	</pre>
		+
		+	e.g.)
		+	<pre>
		+	$ sudo -u postgres psql -f my_db_dump.sql postgres
		+	</pre>

	====Run SQL Script====		====Run SQL Script====

2016-01-01T15:43:20Z

Back Up Database

← Older revision		Revision as of 15:43, 1 January 2016
Line 338:		Line 338:
	<pre>		<pre>
	$ pg_dump -Fc my_db > "my_db_backup.dump.compressed"		$ pg_dump -Fc my_db > "my_db_backup.dump.compressed"
		+	</pre>
		+
		+	* Backup the entire contents of DB cluster
		+	<pre>
		+	pg_dumpall > FILE
	</pre>		</pre>

2016-01-01T15:33:20Z

Back Up Database

← Older revision		Revision as of 15:33, 1 January 2016
Line 330:		Line 330:
	$ sudo -u postgres pg_dump DB_NAME > DB_DUMP_DB_NAME.sql		$ sudo -u postgres pg_dump DB_NAME > DB_DUMP_DB_NAME.sql

-	~~With compression~~	+	To custom-format archive file
	<pre>		<pre>
	pg_dump -Fc DB_NAME > "FILENAME"		pg_dump -Fc DB_NAME > "FILENAME"

2016-01-01T15:29:04Z

Databases

← Older revision		Revision as of 15:29, 1 January 2016
Line 337:		Line 337:
	e.g.)		e.g.)
	<pre>		<pre>
-	$ pg_dump -Fc my_db > "my_db_backup.dump.compressed"	+	$ pg_dump -Fc my_db > "my_db_backup.dump.compressed"
	</pre>		</pre>

	====Restore Database====		====Restore Database====
	$ sudo -u postgres psql --set ON_ERROR_STOP=on DB_NAME < DB_DUMP_DB_NAME.sql		$ sudo -u postgres psql --set ON_ERROR_STOP=on DB_NAME < DB_DUMP_DB_NAME.sql
		+
		+	<pre>
		+	pg_restore --no-owner -U USER -h HOST -d DB_NAME FILENAME
		+	</pre>
		+
		+	e.g.)
		+	<pre>
		+	$ pg_restore --no-owner -U my_db_user -h HOST -d my_db "my_db_backup.dump.compressed"
		+	</pre>


Line 375:		Line 384:
	* To terminate all the processing users of the database, run the following script.		* To terminate all the processing users of the database, run the following script.
	SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname='DB_NAME';		SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname='DB_NAME';
-
-

	===Restart Server===		===Restart Server===

2016-01-01T15:26:33Z

Back Up Database

← Older revision		Revision as of 15:26, 1 January 2016
Line 328:		Line 328:

	====Back Up Database====		====Back Up Database====
-	$ sudo -u postgres pg_dump DB_NAME > DB_DUMP_DB_NAME.sql	+	$ sudo -u postgres pg_dump DB_NAME > DB_DUMP_DB_NAME.sql
		+
		+	With compression
		+	<pre>
		+	pg_dump -Fc DB_NAME > "FILENAME"
		+	</pre>
		+
		+	e.g.)
		+	<pre>
		+	$ pg_dump -Fc my_db > "my_db_backup.dump.compressed"
		+	</pre>

	====Restore Database====		====Restore Database====

2015-05-20T13:25:19Z

Installation

← Older revision		Revision as of 13:25, 20 May 2015
Line 27:		Line 27:
	$ sudo apt-get install postgresql		$ sudo apt-get install postgresql
	</pre>		</pre>
		+
		+	=== For Latest Version ===
		+	<pre>
		+	vim /etc/apt/sources.list.d/pgdg.list
		+	</pre>
		+
		+	add
		+	<pre>
		+	deb http://apt.postgresql.org/pub/repos/apt/ YOUR_UBUNTU_VERSION_HERE-pgdg main
		+	</pre>
		+	e.g.) 14.04
		+	<pre>
		+	deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main
		+	</pre>
		+
		+	Then import the repository signing key.
		+	<pre>
		+	wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \| \
		+	sudo apt-key add -
		+	</pre>
		+
		+	Now update and install.
		+	<pre>
		+	apt-get update
		+	apt-get install postgresql
		+	</pre>
		+
		+	* Reference: http://www.postgresql.org/download/linux/ubuntu/

	==Administration==		==Administration==

2014-09-13T15:28:35Z

Shared Memory Segment Issue

← Older revision		Revision as of 15:28, 13 September 2014
Line 351:		Line 351:
	==Troubleshooting==		==Troubleshooting==
	===Shared Memory Segment Issue===		===Shared Memory Segment Issue===
-	http://askubuntu.com/questions/44373/how-to-fix-postgresql-installation	+	* http://askubuntu.com/questions/44373/how-to-fix-postgresql-installation
-		+	* http://leopard.in.ua/2013/09/05/postgresql-sessting-shared-memory

	==References==		==References==
	{{Reflist\|1}}		{{Reflist\|1}}

2014-09-13T14:49:33Z

← Older revision		Revision as of 14:49, 13 September 2014
Line 348:		Line 348:
	$ sudo apt-get install pgadmin3		$ sudo apt-get install pgadmin3
	</pre>		</pre>
		+
		+	==Troubleshooting==
		+	===Shared Memory Segment Issue===
		+	http://askubuntu.com/questions/44373/how-to-fix-postgresql-installation


	==References==		==References==
	{{Reflist\|1}}		{{Reflist\|1}}

2013-08-06T18:01:28Z

Run SQL Script

← Older revision		Revision as of 18:01, 6 August 2013
Line 315:		Line 315:

	* Run SQL script from within psql		* Run SQL script from within psql
		+	DB_NAME=# \set ON_ERROR_STOP
	DB_NAME=# \i db.sql		DB_NAME=# \i db.sql

Line 320:		Line 321:
	DB_NAME=# type SQL;		DB_NAME=# type SQL;
	e.g.)		e.g.)
-	DB_NAME=# SELECT * FROM some_table;	+	DB_NAME=# SELECT * FROM some_table;
-		+

	====Store the Query Result in File====		====Store the Query Result in File====