PostgreSql copy structure of database without data

Question

I would like to create a copy of the structure only of an existing database without it data to a new database (same server).

I am using PHP 5.2.14

I tried:

CREATE DATABASE base3 WITH TEMPLATE base2 OWNER postgres;

And got this error:

ERROR:  source database "base2" is being accessed by other users
********** Error **********
ERROR: source database "base2" is being accessed by other users SQL state: 55006

I tried adding this code:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'base2' AND procpid <> pg_backend_pid();

and got this error:

ERROR:  function pg_terminate_backend(integer) does not exist
LINE 1: SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM p...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********
ERROR: function pg_terminate_backend(integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 8

Valery Viktorovsky · Accepted Answer · 2015-09-28 07:20:55Z

2

In PostgreSQL 9.2 and above you should use pg_terminate_backend with integer argument and use pid field. Example bellow:

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'base2' AND pid <> pg_backend_pid();

Server Signaling Functions:

pg_cancel_backend and pg_terminate_backend send signals (SIGINT or SIGTERM respectively) to backend processes identified by process ID. The process ID of an active backend can be found from the pid column of the pg_stat_activity view, or by listing the postgres processes on the server (using ps on Unix or the Task Manager on Windows). The role of an active backend can be found from the usename column of the pg_stat_activity view.

edited Sep 28, 2015 at 7:20

answered Sep 27, 2015 at 21:10

Valery Viktorovsky

6,7863 gold badges47 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

David Mulroney Over a year ago

When I use "pid" it says column does not exist. When use: select * it confirms procpid is valid.

David Mulroney Over a year ago

I think the pg_terminate_backend function is only available for PGSql v9.0 and later. I closed down my pgAdminIII. Reopened it and started the original query from the from a different database. Ran through, just copied all the data. Is there a way to "easily" Trunc every table?

wildplasser Over a year ago

No, the column name has changed (somewhere between 8.4 and 9.3) process_pid comes to mind...

Valery Viktorovsky Over a year ago

@user3109003 pg_stat_activity is available from version 8.4

David Mulroney Over a year ago

I am upgrading to 9.4 this weekend! Yay! Going to be nice to be current on this client! Thanks again for the input!

Imran Qamer · Accepted Answer · 2016-10-06 12:23:17Z

1

pg_stat_activity was made available in version 8.4 and ownward.

Older version user can use these functions

as listed on documentation page

https://www.postgresql.org/docs/8.1/static/functions-admin.html

answered Oct 6, 2016 at 12:23

Imran Qamer

2,2633 gold badges33 silver badges54 bronze badges

Collectives™ on Stack Overflow

PostgreSql copy structure of database without data

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related