Craig White wrote:
I wrote a little script to individually back up table schemas, table
data and then vacuum the table and it works nicely but I wanted a way to
query a database and get a text file with just the table names and
cannot figure out a way to do that.
my script looks like this...
(all I want is to get a list of the tables into a text file pg_tables)
#/bin/sh
#
DB_NAME=whatever
#
for i in `cat pg_tables`
do
pg_dump --username=postgres \
--schema=db
--table=$i \
--schema-only \
$DB_NAME > schemas/$i.sql
pg_dump --username=postgres \
--schema=db \
--table=$i \
--data-only \
$DB_NAME > data/$i.sql
vacuumdb --username=postgres \
--dbname=$DB_NAME \
--table=db.$i \
--verbose \
--full
done
Is there a way to do that?
From the command line you can run:
psql mydbname -c "\d"
to get a list of all public table names.
Or just select the table names from pg_tables and send it to a file:
psql myDBname -c "SELECT tablename FROM pg_tables WHERE schemaname =
'someschema';" > my_tables.txt
This works on my 8.1 database on RHEL. You could also use something
similar inside of your script to generate the table names and send them
to pg_dump. As far as I know, if you specify a table name, but don't
specify a schema name to pg_dump, it will automatically dump all tables
with that name, irregardless of what schema they belong to. I'm sure
someone will let me know if that's not correct.......
hth
Ron
Craig
PS there's a lack of cohesion between various commands such as vacuumdb
and pg_dump for things like '--schema'
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
