Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

Hot answers tagged

47

Try here for a basic intro to PostgreSQL and JSON. Also, PostgreSQL documentation is pretty good, so try it here. Check out the pretty_bool option. Your original question was "Is there a way to export postgres table data as JSON". You wanted it in this format {'id':1,'name':'David'} {'id':2,'name':'James'} ... I didn't have a running instance of ...


37

From the CREATE ROLE documentation: Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster. Since pg_dump dumps a single database, you can't extract roles with that utility. The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only ...


16

This is a dump file produced by pg_dump -Fc. To create a text file containing SQL commands from it, use pg_restore. The basic syntax is pg_restore file.dump > file.sql, that is, you don't specify a target database (notice the missing-d option).


15

One of the silent killers of MySQL Connections is the MySQL Packet. Even the I/O Thread of MySQL Replication can be victimized by this. According to the MySQL Documentation You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something ...


12

If you're using psql then there is no reason to use \COPY at all. \t \a \o file.json SELECT row_to_json(r) FROM my_table AS r; This is the same method we use to get png/jpgs/tifs out of the database with PostGIS for quick tests, and also to generate script files with PostgreSQL extensions.


7

Learn and use an ETL tool You'll save yourself a lot of time and pain if you use ETL tools like Pentaho Kettle or Talend Studio to automate the data transfer and merging. They'll happily connect to the Pg instance and the Oracle instance and do the required copying and merging using quite flexible strategies. They can consume CSV dumps too. Use CSV dumps ...


7

CREATE VIEW or CREATE TABLE... AS SELECT have to infer the column types for the relation to create. Sometimes the context is not sufficient to guess a datatype, for example when it's just a string literal. In this case it's created as unknown. Example : test=> CREATE VIEW testview AS SELECT 'bla' AS foobar; WARNING: column "foobar" has type "unknown" ...


7

You can see a rough progress using the TOC list. First, get the TOC list of objects to be restored: pg_restore -l -f list.toc db.dump Then, you can see the TOC list line by line and compare the output of verbose or query pg_stat_activity to see where in the TOC list is pg_restore in. It is just a rough estimate though. First because each item from the ...


6

I'm not sure what's happening, but you're certainly not giving us enough information. ALTER TABLE DISABLE TRIGGER ALL works fine on disabling FOREIGN KEYS. test=# CREATE TABLE table1 ( id serial PRIMARY KEY ); CREATE TABLE test=# CREATE TABLE table2 ( fkey int REFERENCES table1(id) ); CREATE TABLE test=# INSERT INTO table2 VALUES (1); ERROR: insert or ...


5

Your only hope in this scenario is to have the PROCESS privilege. The SUPER privilege allows you to kill processes. Naturally, you don't want that. On the other hand, the PROCESS privilege allows you to see the processlist. According to the MySQL Documentation on the PROCESS Privilege The PROCESS privilege pertains to display of information about the ...


5

What version are you running? I'm not able to reproduce your results on 11.2.0.4. Here's my schema: 16:36:09 system@DB> select owner, sum(bytes)/1024/1024/1024 GB from dba_segments where owner = 'USER' group by owner; OWNER GB ------------------------------ ---------- USER 1156.41901 Here's the ...


5

For me @Vérace's answer didn't maintain the column names, but assigned default names (f1, f2, etc.) instead. I am using PostgreSQL 9.1 with the JSON extension. If you want to export the entire table, there is no need for a subquery. In addition, this will maintain the column names. I used the folowing query: COPY (SELECT row_to_json(t) FROM fred as t) to '/...


5

I will add a special caveat to Verace's answer. You need to do post processing on the outputted JSON file if you have text columns with backslash characters: \. Otherwise you will get duplicate (\ -> \\) at best and completely invalid JSON at worse ie: This: { "f1" : "crap\""}. Becomes { "f1" : "crap\\""}. Which looks fine but is completely invalid ...


5

In SQL Server Management Studio (SSMS), you can generate a script that would create the structural components of your database (tables and views, as well as users, functions, and procedures). You could use the script(s) returned on your development DB. To run this, right-click on your database in SSMS, and choose Tasks => Generate scripts.... This opens a ...


4

You cannot just "open" a database backup. You have to restore the database. For this, you first need to install PostgreSQL. The latest version should be capable of taking backups from older versions, too. But older version may have problems with newer features in the backup. So your version should be at least as recent as the source. How you install ...


4

Regarding the password, you have two choices: An environment variable named PGPASSWORD: http://www.postgresql.org/docs/current/static/libpq-envars.html A configuration file containing the password: http://www.postgresql.org/docs/current/static/libpq-pgpass.html I don't understand why you need to run the pg_dump remotely? You can run pg_dump on any computer ...


4

dezso was right, the PGPASSWORD environment variable is present when executing Tomcat from a single shell, but not when it's started as a service. The solution is to put the value in the process environment: ProcessBuilder builder = new ProcessBuilder( "C:/postgres9/bin/pg_dump.exe", "-U", "myuser", "-h" , "localhost", "mydb"); ...


4

You should really increase max_locks_per_transaction. As specified in http://www.postgresql.org/docs/9.0/static/runtime-config-locks.html , changing this parameter may require to also change the System V shared memory. You have to increase that value also. In OS X this can be done as explained in http://www.postgresql.org/docs/9.0/static/kernel-resources....


4

The postgres database is just the default database that is created during initdb. It has no special meaning. If you created a different one where all your data is kept and you never created tables in the postgres database, then there is no need to dump it. I don't know what pg_upgradecluster is. The default tool to upgrade the data directory (apart from ...


4

Ok here's what I did: I was in the ORANT\BIN folder. I had to figure out what the SID was for the DB. Did that via an Oracle client which I tried to connect remotely to Oracle on the server. set oracle_sid=a exp73.exe System/manager file=myexp13xxxx.dmp full=y log=mylog130527.log


4

The main indicator is the exit status of the pg_dump command. If it's non-zero, then something went wrong otherwise it worked all along. This is the implicit contract between any command and the shell, and breaching it would be a bug. And it's also testable when the sub-command could't be launched at all. Here's a basic skeleton in Unix shell that tests the ...


4

The temporary files are a side effect of the --format=tar option. A backup in default plain format goes through without temporary tables in the local drive. Omit the option to produce a backup in plain format without temporary local files.


4

how to create "empty" or "blank" databases I would use pg_dump with the --schema-only option to create the dump for an empty database: pg_dump mydb -s > /mypath/myfile.sql Using Linux, you could also pipe the output to psql and create a new, empty database in the same (or any other) database cluster right away. Example for the same cluster: createdb ...


4

It sounds like you're trying to refresh a database that's a snapshot of another DB elsewhere, and do so without interrupting read access to the first DB. If so, the easiest way is actually to do it live in the snapshot DB. Just begin a transaction, truncate all the tables, and populate them with new data, then commit. Concurrent transactions will see the ...


4

You are essentially attempting to dump the entire oplog and hence paging in all of it, not just the most recent data. That is going to cause a lot of IO, especially when your oplog is too large to fit into memory. On a smaller database it's not a problem because the oplog is relatively small. The reason why it's dumping the full table is because I believe ...


4

Does it exist on Oracle? Similar utility exists in Oracle environment called Data Pump. How can it be done? Use expdp command line tool to perform full database export. Example: expdp hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=YES NOLOGFILE=YES Then can use impdp to import it in another database. Example: impdp hr DUMPFILE=dpump_dir1:...


4

You can use set timestamp <unix_epoch> which will make now() be fixed -- always return that value. See https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_timestamp Note that this will not affect sysdate(). To fix sysdate, you can start MySQL with the --sysdate-is-now option which will make sysdate() return the same value as ...


3

If you are using the old imp method the users and tablespaces must be created first. This includes grants, database links and any advanced queues you may be using. A sample script could look like this in Windows imp a_DBA_user/apassword LOG =C:\dump\logs\import.log file =user01.dmp fromuser=user_name touser=user_name The newer datapump method works ...


3

Recently I had a similar problem … Here goes all the required steps with comments … ! [On Source] ============================================================================ 1) Checks on Source a. Run (on solaris prompt) On source machine Execute: uname -a In my case the output was: SunOS clusterz1 5.10 Generic_142900-03 ...


3

version, OS, tool are helping things to help you solve this. I guess OS is Windows. version 9i. export full. If this is the case: connect to the server, set ORACLE_HOME=/where/is/oracle/home/ set PATH=%ORACLE_HOME%/bin;%PATH% set ORACLE_SID=ORCL -> or your ORACLE_SID name imp file=x.dmp logfile=imp_x.log full=y imp help=y gives some clues about the ...


Only top voted, non community-wiki answers of a minimum length are eligible