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

@@ Line 27: / Line 27: @@
 $ sudo apt-get install postgresql
 </pre>
 ==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====